[MSSQL] 컬럼 문자열 합치기 (STRING_AGG(), STUFF())

여러 행의 컬럼 값을 하나의 문자열로 합칠 때 사용.
 
2017 이상 부터 지원되는 STRING_AGG()를 사용하는 것이 제일 간편하며, 
아래 샘플 쿼리로 사용법에 대해 바로 확인 가능하다.

1) STRING_AGG()
+ SQL Server 2017 이상의 버전에서 지원
+ GROUP BY 절과 함께 사용 가능 & ORDER BY 절을 사용하여 정렬이 가능하며 ORDER BY 절은 생략할 수 있음.
+ 형식) STRING_AGG("합칠 대상 컬럼명", "구분자") [WITHIN GROUP(ORDER BY "정렬하고자하는 컬럼명")]

-- ---------------------------------------- -- #1. [LastName] 문자열 합치기 -- ---------------------------------------- -- 1-1. 기본 조회 (특정 컬럼 문자열 합치기) SELECT STRING_AGG ([LastName], ',') AS [LastName_All] FROM (                 VALUES (1, 'Kim'), (1, 'Lee'), (2, 'Gu'), (2, 'Baek'), (3, 'Seo') ) AS LastNameTable ([id],[LastName]) ; -- 1-2. id기준으로 Grouping하여 조회  : GROUP BY 추가 SELECT [id]                , STRING_AGG ([LastName],', ') AS  [LastName_All_GroupBy] FROM (                 VALUES (1, 'Kim'), (1, 'Lee'), (2, 'Gu'), (2, 'Baek'), (3, 'Seo') ) AS LastNameTable ([id],[LastName]) GROUP BY [id] ; -- --------------------------------------------------- -- #2. [LastName] 컬럼으로 정렬하여 조회 -- --------------------------------------------------- -- 2-1. 기본 조회  (특정 컬럼 문자열 합치기) SELECT STRING_AGG([LastName],', ')  WITHIN GROUP (ORDER BY [LastName]) AS [LastName_All_Ordering] FROM (                VALUES (1, 'Kim'), (1, 'Lee'), (2, 'Gu'), (2, 'Baek'), (3, 'Seo') ) AS LastNameTable ([id], [LastName]) ; -- 2-2.  id기준으로 Grouping하여 조회 : GROUP BY 추가 SELECT [id]                , STRING_AGG([LastName],', ')  WITHIN GROUP (ORDER BY [LastName]) AS [LastName_All_GroupBy&Ordering] FROM (                VALUES (1, 'Kim'), (1, 'Lee'), (2, 'Gu'), (2, 'Baek'), (3, 'Seo') ) AS LastNameTable ([id], [LastName]) GROUP BY [id] ;

 
 
2) STUFF() 함수와 FOR XML PATH 사용 
+ 조회 시, 중복 제거를 위해 GROUP BY 또는 DISTINCT 사용 필요
+ 형식) STUFF(문자열, 시작위치, 문자길이, 치환문자)

-- ---------------------------------------- -- #1. [LastName] 문자열 합치기 -- ---------------------------------------- -- 1-1. 기본 조회  (특정 컬럼 문자열 합치기) SELECT DISTINCT STUFF(                                               ( SELECT ','+ CONVERT(NVARCHAR,[LastName])                                                  FROM (                                                                VALUES (1, 'Kim'), (1, 'Lee'), (2, 'Gu'), (2, 'Baek'), (3, 'Seo')                                                   ) AS LastNameTable ([id],[LastName])                                                 FOR XML PATH('')                                               )                                               , 1, 1, '') AS [LastName] FROM (               VALUES (1, 'Kim'), (1, 'Lee'), (2, 'Gu'), (2, 'Baek'), (3, 'Seo') ) AS A ([id],[LastName])  ; -- 1-2. id기준으로 Grouping하여 조회 : DISTINCT  또는 GROUP BY 추가 SELECT DISTINCT  A.[id]                , STUFF(                                ( SELECT ','+ CONVERT(NVARCHAR,[LastName])                                   FROM (                                                  VALUES (1, 'Kim'), (1, 'Lee'), (2, 'Gu'), (2, 'Baek'), (3, 'Seo')                                    ) AS LastNameTable ([id],[LastName])                                   WHERE [id]   = A.[id]                                   FOR XML PATH('')                                )                              , 1, 1, '') AS [LastName] FROM (               VALUES (1, 'Kim'), (1, 'Lee'), (2, 'Gu'), (2, 'Baek'), (3, 'Seo') ) AS A ([id],[LastName])  -- GROUP BY A.[id] ; -- ----------------------------------------------------- -- #2. [LastName] 컬럼으로 정렬하여 조회 -- ----------------------------------------------------- -- 2-1. 기본 조회  (특정 컬럼 문자열 합치기) SELECT DISTINCT STUFF(                                               ( SELECT ','+ CONVERT(NVARCHAR,[LastName])                                                  FROM (                                                               VALUES (1, 'Kim'), (1, 'Lee'), (2, 'Gu'), (2, 'Baek'), (3, 'Seo')                                                   ) AS LastNameTable ([id],[LastName])                                                  ORDER BY [LastName]                                                   FOR XML PATH('')                                                )                                              , 1, 1, '') AS [LastName] FROM (               VALUES (1, 'Kim'), (1, 'Lee'), (2, 'Gu'), (2, 'Baek'), (3, 'Seo') ) AS A ([id],[LastName])  ; -- 2-2. id기준으로 Grouping하여 조회 : DISTINCT  또는 GROUP BY 추가 SELECT DISTINCT  A.[id]                , STUFF(                                 ( SELECT ','+ CONVERT(NVARCHAR,[LastName])                                    FROM (                                                  VALUES (1, 'Kim'), (1, 'Lee'), (2, 'Gu'), (2, 'Baek'), (3, 'Seo')                                    ) AS LastNameTable ([id],[LastName])                                    WHERE [id]   = A.[id]                                    ORDER BY [LastName]                                    FOR XML PATH('')                                 )                               , 1, 1, '') AS [LastName] FROM (               VALUES (1, 'Kim'), (1, 'Lee'), (2, 'Gu'), (2, 'Baek'), (3, 'Seo') ) AS A ([id],[LastName])  -- GROUP BY A.[id] ;