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 */ |
-------------------------------------------------------------------------------------------- -- 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 |
-------------------------------------------------------------------------------------------- --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 |
-------------------------------------------------------------------------------------------- --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 |
-------------------------------------------------------------------------------------------- --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 |
'연구개발 > SQL2008' 카테고리의 다른 글
XML로 데이터를 만들어서 SP에서 처리하는 방법(sp_xml_removedocument) (0) | 2011.03.21 |
---|---|
xp_cmdshell enable and disable (0) | 2010.05.25 |
SQL Server를 실행하는 서버 사이에서 데이터베이스를 이동할 때 사용 권한 문제를 해결하는 방법 (0) | 2010.05.23 |
IP 주소 범위 체크 (0) | 2010.05.20 |
오라클의 LPAD 함수 (0) | 2010.05.20 |