SQL 쿼리를 사용하여 쉼표로 구분된 목록을 만들려면 어떻게 해야 합니까?

SQL 쿼리를 사용하여 쉼표로 구분된 목록을 만들려면 어떻게 해야 합니까?

다음과 같은 세 개의 테이블이 있습니다.

• 응용 프로그램(ID, 이름)
• 리소스(ID, 이름)
• 애플리케이션 리소스(id, app_id, resource_id)

모든 리소스 이름의 테이블을 GUI에 표시합니다.각 행의 하나의 셀에 해당 리소스의 모든 애플리케이션(쉼표로 구분)을 나열합니다.

모든 리소스를 가져와야 하고 각 리소스에 대한 모든 애플리케이션을 가져와야 하는데 SQL에서 이를 수행하는 가장 좋은 방법은 무엇입니까?

리소스에서 선택 *를 먼저 실행한 다음 각 리소스를 순환하고 리소스별로 별도의 쿼리를 수행하여 해당 리소스에 대한 응용 프로그램 목록을 가져오시겠습니까?

제가 이것을 한 번의 질문으로 할 수 있는 방법이 있나요?

MySQL

  SELECT r.name,
         GROUP_CONCAT(a.name SEPARATOR ',')
    FROM RESOURCES r
    JOIN APPLICATIONSRESOURCES ar ON ar.resource_id = r.id
    JOIN APPLICATIONS a ON a.id = ar.app_id
GROUP BY r.name

SQL Server(2005+)

SELECT r.name,
       STUFF((SELECT ',' + a.name
               FROM APPLICATIONS a
               JOIN APPLICATIONRESOURCES ar ON ar.app_id = a.id
              WHERE ar.resource_id = r.id
           GROUP BY a.name
            FOR XML PATH(''), TYPE).value('text()[1]','NVARCHAR(max)'), 1, LEN(','), '')
 FROM RESOURCES r

SQL 서버(2017+)

  SELECT r.name,
         STRING_AGG(a.name, ',')
    FROM RESOURCES r
    JOIN APPLICATIONSRESOURCES ar ON ar.resource_id = r.id
    JOIN APPLICATIONS a ON a.id = ar.app_id
GROUP BY r.name

오라클

Oracle에서 문자열 집합/연속에 대한 내용을 읽어보는 것이 좋습니다.

참고:

이 방법은 잘못된 결과나 비결정론적 결과를 제공할 수 있으므로 권장되지 않습니다.
이 내용은 StackOverflow 및 DBA에 문서화되어 있습니다.

COALESCE를 사용하여 SQL Server에서 쉼표로 구분된 문자열 작성
http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string

예:

DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 
   CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @EmployeeList

데이터베이스에 구애받지 않는 방식으로 이 작업을 수행할 수 있는 솔루션이 있는지 모르겠습니다. 대부분의 경우 어떤 형태로든 문자열을 조작해야 하며 일반적으로 공급업체마다 다릅니다.

SQL Server 2005 이상의 경우 다음을 사용할 수 있습니다.

SELECT
     r.ID, r.Name,
     Resources = STUFF(
       (SELECT ','+a.Name
        FROM dbo.Applications a
        INNER JOIN dbo.ApplicationsResources ar ON ar.app_id = a.id
        WHERE ar.resource_id = r.id
        FOR XML PATH('')), 1, 1, '')
FROM
     dbo.Resources r

SQL Server 2005를 사용합니다.FOR XML PATH구성 - 하위 항목(지정된 리소스에 대한 응용 프로그램)을 쉼표로 구분된 목록으로 나열합니다.

마르크

당신이 원하는 것은 다음과 같습니다.

SELECT ItemName, GROUP_CONCAT(DepartmentId) FROM table_name GROUP BY ItemName

MySQL을 사용하는 경우

언급

• http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

SQL Server 가정:

테이블 구조:

CREATE TABLE [dbo].[item_dept](
    [ItemName] char(20) NULL,
    [DepartmentID] int NULL   
)

쿼리:

SELECT ItemName,
       STUFF((SELECT ',' + rtrim(convert(char(10),DepartmentID))
        FROM   item_dept b
        WHERE  a.ItemName = b.ItemName
        FOR XML PATH('')),1,1,'') DepartmentID
FROM   item_dept a
GROUP BY ItemName

결과:

ItemName    DepartmentID
item1       21,13,9,36
item2       4,9,44

검색을 위해 다음과 같은 방법으로 작성할 수 있을 것 같습니다(아래 코드는 예시입니다. 필요에 따라 수정하십시오).

Create FUNCTION dbo.ufnGetEmployeeMultiple(@DepartmentID int)
RETURNS VARCHAR(1000) AS

BEGIN

DECLARE @Employeelist varchar(1000)

SELECT @Employeelist = COALESCE(@Employeelist + ', ', '') + E.LoginID
FROM humanresources.Employee E

Left JOIN humanresources.EmployeeDepartmentHistory H ON
E.BusinessEntityID = H.BusinessEntityID

INNER JOIN HumanResources.Department D ON
H.DepartmentID = D.DepartmentID

Where H.DepartmentID = @DepartmentID

Return @Employeelist

END

SELECT D.name as Department, dbo.ufnGetEmployeeMultiple (D.DepartmentID)as Employees
FROM HumanResources.Department D

SELECT Distinct (D.name) as Department, dbo.ufnGetEmployeeMultiple (D.DepartmentID) as 
Employees
FROM HumanResources.Department D

다음 버전의 SQL Server에서 다음 작업을 수행할 수 있습니다.

SELECT r.name,
       STRING_AGG(a.name, ',')
FROM   RESOURCES r
       JOIN APPLICATIONSRESOURCES ar
         ON ar.resource_id = r.id
       JOIN APPLICATIONS a
         ON a.id = ar.app_id
GROUP  BY r.name 

이전 버전의 제품의 경우 이 문제에 대한 접근 방식이 상당히 다양합니다.이에 대한 훌륭한 리뷰는 다음 기사에 나와 있습니다.Transact-SQL에서 행 값을 연결하는 중입니다.

• 항목 수를 알 수 없는 경우 값 연결

  • 재귀적 CTE 방법
  • 블랙박스 XML 메서드
  • 공용 언어 런타임 사용
  • 재귀가 있는 스칼라 UDF
  • WHIN 루프가 있는 테이블 값 UDF
  • 동적 SQL
  • 
    .

• 신뢰할 수 없는 접근 방식

  • t-SQL 업데이트 확장이 있는 스칼라 UDF
  • SELECT에 변수 연결이 있는 스칼라 UDF

MySQL

  SELECT r.name,
         GROUP_CONCAT(a.name SEPARATOR ',')
    FROM RESOURCES r
    JOIN APPLICATIONSRESOURCES ar ON ar.resource_id = r.id
    JOIN APPLICATIONS a ON a.id = ar.app_id
GROUP BY r.name

**

---

MS SQL 서버

SELECT r.name,
       STUFF((SELECT ','+ a.name
               FROM APPLICATIONS a
               JOIN APPLICATIONRESOURCES ar ON ar.app_id = a.id
              WHERE ar.resource_id = r.id
           GROUP BY a.name
            FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
 FROM RESOURCES r
 GROUP BY deptno;

---

오라클

  SELECT r.name,
         LISTAGG(a.name SEPARATOR ',') WITHIN GROUP (ORDER BY a.name)
  FROM RESOURCES r
        JOIN APPLICATIONSRESOURCES ar ON ar.resource_id = r.id
        JOIN APPLICATIONS a ON a.id = ar.app_id
  GROUP BY r.name;

DB에 구애받지 않는 방식으로 수행할 수 있는 방법은 없습니다.따라서 다음과 같은 전체 데이터 세트를 확보해야 합니다.

select 
  r.name as ResName, 
  a.name as AppName
from 
  Resouces as r, 
  Applications as a, 
  ApplicationsResources as ar
where
  ar.app_id = a.id 
  and ar.resource_id = r.id

그런 다음 ResName별로 그룹화하면서 AppName을 프로그래밍 방식으로 연결합니다.

불가지론자가 되려면, 뒤로 물러서서 펀트하세요.

Select a.name as a_name, r.name as r_name
  from ApplicationsResource ar, Applications a, Resources r
 where a.id = ar.app_id
   and r.id = ar.resource_id
 order by r.name, a.name;

이제 서버 프로그래밍 언어를 사용하여 r_name이 지난번과 동일한 동안 a_names를 연결합니다.

SQL Server에서 이 작업을 수행합니다.

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Convert(nvarchar(8),DepartmentId)
FROM Table
SELECT @listStr

언급URL : https://stackoverflow.com/questions/1817985/how-do-i-create-a-comma-separated-list-using-a-sql-query