반응형



> Link : http://blogs.conchango.com/jamiethomson/archive/2007/03/20/T_2D00_SQL_3A00_-Produce-a-comma-delimited-list-of-values-from-a-rowset.aspx
자주가는 블로그에 재미있는 꺼리가 올라와서 간단히 정리해봤습니다.
 
 
다음과 같은 형태의 데이터를
    
다음과 같이 같은 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이 좋게 보이네요..^^ 여러분은 어떤 방법이 제일 좋으신지요..
더 좋은 방법 있으면 리플 달아주세요.
반응형

+ Recent posts