[2k] 여러 값(rows)을 구분자로 연결된 하나의 값으로 변경하기

/**********************************************************************************************
-- Title : [2k] 여러 값(rows)을 구분자로 연결된 하나의 값으로 변경하기
-- Reference : dBRang.com
-- Key word : for xml path, 구분자, 멀티값, 싱글값 multiple rows single row
**********************************************************************************************/

* dbrang.tistory.com/41 도 참조.

--테이블 및 데이터 생성
CREATE TABLE #applicant
( wkey CHAR(16)
, ord INT
, names VARCHAR(10)
);
GO

INSERT #applicant
SELECT 'EP0000000000A1P', 1, '김재원' UNION ALL
SELECT 'EP0000000000A1P', 2, '최보현' UNION ALL
SELECT 'EP0000000000A1P', 3, '정명훈' UNION ALL
SELECT 'EP0000000000A1P', 4, '최명환' UNION ALL
SELECT 'EP0000000000A1P', 5, '안인옥' UNION ALL
SELECT 'US0000000000B1P', 1, '정성현' UNION ALL
SELECT 'US0000000000B1P', 2, '성재모' UNION ALL
SELECT 'US0000000000B1P', 3, '이규태';
GO

SELECT * FROM #applicant;
GO
/*
wkey             ord         names
---------------- ----------- ----------
EP0000000000A1P  1           김재원
EP0000000000A1P  2           최보현
EP0000000000A1P  3           정명훈
EP0000000000A1P  4           최명환
EP0000000000A1P  5           안인옥
US0000000000B1P  1           정성현
US0000000000B1P  2           성재모
US0000000000B1P  3           이규태
*/

-- Case 1) 엽기 쿼리 활용(Ref.sqler.pe.kr)
DECLARE @rtnString varchar(100);

SET @rtnString = ''

SELECT @rtnString = @rtnString + names + ','
FROM #applicant
WHERE wkey = 'EP0000000000A1P';

SELECT LEFT(@rtnString, LEN(@rtnString)-1) "출원인";
GO
/*
출원인
----------------------------------------
김재원,최보현,정명훈,최명환,안인옥
*/

-- Case 2) FOR XML PATH활용(몇천건에서 몇만건의 경우 1번보다 2번이 훨씬 빠르다..) 
SELECT DISTINCT wkey
     , STUFF((SELECT ',' + names AS [text()]
              FROM #applicant b
              WHERE b.wkey = a.wkey
              FOR XML PATH('')),1,1,'') AS 출원인
 FROM #applicant a


-- Case 3) & -> &amp로 바뀌는 현상 제거
SELECT DISTINCT wkey
     , STUFF(((SELECT ' / ' + names AS [text()]
              FROM #applicant b
              WHERE b.wkey = a.wkey
              FOR XML PATH(''),type).value('.','VARCHAR(max)')
             ),1,2,''
            ) AS 출원인
 FROM #applicant a;

-- Case 4) Group by한 결과를 병합 할 때
create table yourtable
(player varchar(10), score varchar(10));

insert into yourtable
select '001', '10' union all
select '001', '10' union all
select '001', '20' union all
select '002', '20' union all
select '002', '30' union all
select '003', '10' union all
select '003', '10' union all
select '003', '10' union all
select '004', '10' union all
select '004', '20' union all
select '004', '20' union all
select '004', '10';

select * from yourtable;

select player
    , stuff((SELECT distinct ', ' + cast(score as varchar(10))
              FROM yourtable t2
              where t2.player = t1.player
              FOR XML PATH('')),1,1,'') "merge"
from yourtable t1
group by player;