■ XMLAGG, XMLELEMENT 함수를 사용해 여러 행의 데이터를 한 컬럼에 결합하는 방법을 보여준다.
▶ 예제 코드 (SQL)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
WITH SOURCE_TABLE AS ( SELECT '고구려' COUNTRY, '1대' ST, '동명성왕' KING_NAME FROM DUAL UNION ALL SELECT '고구려' COUNTRY, '3대' ST, '대무신왕' KING_NAME FROM DUAL UNION ALL SELECT '백제' COUNTRY, '1대' ST, '온조왕' KING_NAME FROM DUAL UNION ALL SELECT '고구려' COUNTRY, '2대' ST, '유리왕' KING_NAME FROM DUAL UNION ALL SELECT '백제' COUNTRY, '3대' ST, '기루왕' KING_NAME FROM DUAL UNION ALL SELECT '신라' COUNTRY, '1대' ST, '남해왕' KING_NAME FROM DUAL UNION ALL SELECT '신라' COUNTRY, '1대' ST, '박혁거세' KING_NAME FROM DUAL UNION ALL SELECT '백제' COUNTRY, '2대' ST, '다루왕' KING_NAME FROM DUAL UNION ALL SELECT '신라' COUNTRY, '1대' ST, '유리이사금' KING_NAME FROM DUAL ) SELECT COUNTRY ,SUBSTR(XMLAGG(XMLELEMENT(COL ,',', KING_NAME) ORDER BY ST).EXTRACT('//text()').GETSTRINGVAL(), 2) KING_NAME FROM SOURCE_TABLE GROUP BY COUNTRY; /* COUNTRY KING_NAME ------- -------------------------- 고구려 동명성왕,유리왕,대무신왕 백제 온조왕,다루왕,기루왕 신라 남해왕,유리이사금,박혁거세 */ |