WITH T(TYPE, NAME) AS
(
SELECT '과일', '사과' FROM dual
UNION ALL SELECT '과일', '레몬'FROM dual
UNION ALL SELECT '과일', '포도' FROM dual
UNION ALL SELECT '과일', '참외' FROM dual
UNION ALL SELECT '채소', '오이' FROM dual
UNION ALL SELECT '채소', '당근' FROM dual
UNION ALL SELECT '채소', '호박' FROM dual
)
SELECT type
, STUFF((SELECT ',' + name
FROM t
WHERE type = a.type
FOR XML PATH('')
), 1, 1, '') name_MSSQL
FROM t a
GROUP BY type
위에 쿼리를실행하면 아래처럼 조회가되는데요
type name_MSSQL
과일 사과,레몬,포도,참외
채소 오이,당근,호박
이걸 다시 A라는 컬럼으로 한줄로 만들어줄수있나요?
A
사과,레몬,포도,참외(과일) 오이,당근,호박(채소)
위쿼리출처는
입니다.
자답입니다 ^^;
WITH T(TYPE, NAME) AS
(
SELECT '과일', '사과' FROM dual
UNION ALL SELECT '과일', '레몬'FROM dual
UNION ALL SELECT '과일', '포도' FROM dual
UNION ALL SELECT '과일', '참외' FROM dual
UNION ALL SELECT '채소', '오이' FROM dual
UNION ALL SELECT '채소', '당근' FROM dual
UNION ALL SELECT '채소', '호박' FROM dual
) , T2 AS (
SELECT type
, STUFF((SELECT ',' + name
FROM t
WHERE type = a.type
FOR XML PATH('')
), 1, 1, '') name_MSSQL
FROM t a
GROUP BY type
)
SELECT STUFF((SELECT ',' + name_MSSQL
FROM t2
WHERE type = a.type
FOR XML PATH('')
), 1, 1, '')+'('+type+')' name_MSSQL
FROM T2 A
;