(펌) T_SQL 같은 그룹의 데이터를 붙여서 출력하기

출처 : http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005TSQL&intPage=1&intCategory=0&strSearchCategory=|s_name|s_subject|&strSearchWord=&intSeq=972

자주가는 블로그에 재미있는 꺼리가 올라와서 간단히 정리해봤습니다.

 

 

다음과 같은 형태의 데이터를

다음과 같이 같은 id 그룹별로 쉼표(,)로 붙여서 출력하는 문제입니다.

 

조건은 

  a. 커서를 쓰면 안되며,

  b. 임시 테이블 또는 테이블 변수와 같은 것 사용 없이 쿼리 한 방으로 결과 뽑기

  c. 대신 SQL 2005의 CTE 등은 이용해도 되기~ 입니다.

 

아래의 여러 고수들이 제시한 방법들을 보시기 전에 먼저 한 번 고민해 보시길 바랍니다.^^

 

 

테스트용 데이터 생성하기

 

USE TEMPDB GO     IF EXISTS (SELECT * FROM SYS.TABLES WHERE NAME = 't1' AND type = 'U')         DROP TABLE t1 Go     CREATE TABLE t1 (id INT, NAME VARCHAR(MAX)) INSERT t1 values (1,'Jamie') INSERT t1 values (1,'Joe') INSERT t1 values (1,'John') INSERT t1 values (2,'Sai') INSERT t1 values (2,'Sam') INSERT t1 values (3,'Roger') INSERT t1 values (4,'Walter') INSERT t1 values (4,'Teddy') GO   SELECT * FROM T1 GO /* id      NAME ----------------- 1       Jamie 1       Joe 1       John 2       Sai 2       Sam 3       Roger 4       Walter 4       Teddy */

 

 

 

 

[방법 1]

-------------------------------------------------------------------------------------------- -- Nick Barclay -------------------------------------------------------------------------------------------- WITH ConcatNamesCTE (id, [NAME], rn) AS (         SELECT id, [NAME], rn         FROM         (                SELECT id, [NAME],                        row_number() OVER(PARTITION BY id ORDER BY id) AS rn                FROM t1         ) a         WHERE rn = 1         UNION ALL         SELECT b.id, cn.[NAME] + ',' + b.[NAME], b.rn         FROM         (                SELECT id, [NAME],                        row_number() OVER(PARTITION BY id ORDER BY id) AS rn                FROM t1         ) b         INNER JOIN ConcatNamesCTE cn ON cn.id = b.id AND cn.rn + 1 = b.rn ) SELECT d.id, d.[NAME] FROM (         SELECT MAX(rn) AS rn, id         FROM ConcatNamesCTE         GROUP BY id ) c INNER JOIN ConcatNamesCTE d ON d.id = c.id AND d.rn = c.rn ORDER BY id GO

 

 

[방법 2]

-------------------------------------------------------------------------------------------- --Adrian Downes -------------------------------------------------------------------------------------------- SELECT res.id, MAX(res.[NAME]) AS [NAME] FROM (         SELECT c.id,         CASE                WHEN PATINDEX('%' + d.[NAME] + '%', c.[NAME]) = 0 AND c.id = d.id                        THEN c.[NAME] + ', ' + d.[NAME]                ELSE c.[NAME]         END AS [NAME]         FROM         (                SELECT a.id, MIN(a.[NAME]) AS [NAME]                FROM                (                        SELECT y.id,                        CASE                                WHEN PATINDEX('%' + z.[NAME] + '%', y.[NAME]) = 0 AND y.id = z.id                               THEN y.[NAME] + ', ' + z.[NAME]                               END AS [NAME]                        FROM t1 y                        INNER JOIN t1 z ON y.id = z.id                ) a                GROUP BY a.id         ) c         INNER JOIN t1 d ON c.id = d.id ) res GROUP BY res.id GO

 

 

[방법 3]

-------------------------------------------------------------------------------------------- --Jamie Hunter -------------------------------------------------------------------------------------------- SELECT         DISTINCT         id         , STUFF(                (SELECT ',' + name AS [text()] FROM t1 b WHERE b.id = a.id FOR XML PATH(''))                ,  1,  1,  ''                ) AS name_csv FROM t1 a ORDER BY 1 GO

 

 

[방법 4]

-------------------------------------------------------------------------------------------- --Rick R -------------------------------------------------------------------------------------------- WITH t2 AS (         SELECT id, MIN(name) name         FROM t1 GROUP BY id         UNION ALL         SELECT a.id, a.[NAME] + ',' + b.[NAME] AS name         FROM t1 a JOIN t2 b ON a.id = b.id AND a.[NAME] > b.[NAME] ) SELECT id, MAX(name) FROM t2 GROUP BY id GO

 

 

 

개인적으로는 방법 3이 좋게 보이네요..^^ 여러분은 어떤 방법이 제일 좋으신지요..

더 좋은 방법 있으면 리플 달아주세요.