[MSSQL] 세로 데이터 가로로 출력 (데이터 한줄로 출력) STUFF, FOR XML PATH

[MSSQL] 세로 데이터 가로로 출력 (데이터 한줄로 출력) STUFF, FOR XML PATH

http://ggmouse.tistory.com/127

DECLARE @string AS VARCHAR(1000);

SET @string = '';

 

WITH TABLE_A (NAME) AS (

    SELECT '손꽁쥐' UNION ALL

    SELECT '윤선생' UNION ALL

    SELECT '황박사'

)

 

SELECT @string = NAME + ', ' + @string FROM TABLE_A

SELECT @string AS '가로출력' 

--  

WITH TABLE_A (CLASS, NAME) AS (

    SELECT 'A', '손꽁쥐' UNION ALL

    SELECT 'B', '윤선생' UNION ALL

    SELECT 'A', '황박사'

)

SELECT * FROM

(

    SELECT CLASS, STUFF((SELECT ',' + NAME FROM TABLE_A WHERE CLASS = A.CLASS FOR XML PATH('')), 1, 1, '') AS 'Result'

    FROM TABLE_A AS A

) A

GROUP BY A.CLASS, A.Result