Stuff

여러 행으로 되어 있는 데이터들을 1개의 열로 바꾸는 구문

CREATE TABLE TMP_TBL (
	SEQ INT IDENTITY(1, 1),
	GROUPID INT,
	C_VAL VARCHAR(10),
	T_VAL VARCHAR(10),
	M_VAL VARCHAR(10),
	REG_DT DATETIME DEFAULT(GETDATE())
)

INSERT INTO TMP_TBL (GROUPID, C_VAL, T_VAL, M_VAL) VALUES ('1', '1111', 'TTTT', 'MMMM');
INSERT INTO TMP_TBL (GROUPID, C_VAL, T_VAL, M_VAL) VALUES ('1', '2222', '1212', 'MMMM');
INSERT INTO TMP_TBL (GROUPID, C_VAL, T_VAL, M_VAL) VALUES ('1', '3333', '3232', 'MMMM');

INSERT INTO TMP_TBL (GROUPID, C_VAL, T_VAL, M_VAL) VALUES ('2', 'ABC', 'SSS', '333');
INSERT INTO TMP_TBL (GROUPID, C_VAL, T_VAL, M_VAL) VALUES ('2', 'DDD', 'WWW', '333');
INSERT INTO TMP_TBL (GROUPID, C_VAL, T_VAL, M_VAL) VALUES ('2', 'CCC', 'QQQ', '333');

INSERT INTO TMP_TBL (GROUPID, C_VAL, T_VAL, M_VAL) VALUES ('3', 'DSFSD', '32131', 'QWEWQEWQ');
INSERT INTO TMP_TBL (GROUPID, C_VAL, T_VAL, M_VAL) VALUES ('3', 'FDS', '32131', 'DDDDSS');
INSERT INTO TMP_TBL (GROUPID, C_VAL, T_VAL, M_VAL) VALUES ('3', 'DSFSD', 'SSSSS', 'QWEWQEWQ');


SELECT GROUPID,
	STUFF((SELECT DISTINCT ',' + C_VAL FROM TMP_TBL AS BBB WHERE BBB.GROUPID = AAA.GROUPID FOR XML PATH('')), 1, 1, '') AS C_VAL,
	STUFF((SELECT DISTINCT ',' + T_VAL FROM TMP_TBL AS BBB WHERE BBB.GROUPID = AAA.GROUPID FOR XML PATH('')), 1, 1, '') AS T_VAL,
	STUFF((SELECT DISTINCT ',' + M_VAL FROM TMP_TBL AS BBB WHERE BBB.GROUPID = AAA.GROUPID FOR XML PATH('')), 1, 1, '') AS M_VAL
	FROM TMP_TBL AS AAA
GROUP BY AAA.GROUPID


-- Result

GROUPID			C_VAL				T_VAL				M_VAL
------------------------------------------------------------------------------------------
1				1111,2222,3333		1212,3232,TTTT			MMMM
2				ABC,CCC,DDD			QQQ,SSS,WWW			333
3				DSFSD,FDS			32131,SSSSS			DDDDSS,QWEWQEWQ

(3개 행이 영향을 받음)