여러 행을 하나의 컬럼으로 합치기
GROUP BY 절로 묶을때 여러행의 정보를 하나의 컬럼에 나타내야할 필요가 있다.
아래와 같은 방법으로 손쉽게 여러행의 데이터를 한 컬럼으로 합칠 수 있다. .
1. XMLAGG, XMLELEMENT
order by 에 의한 조합 순서 조정이 필요한 경우
2. WM_CONCAT
묶이는 순서대로 자동
-- SAMPLE QUERY
WITH COL_LIST_TEMP AS (
SELECT '1' AS T_CD , 4 AS seq , '일' AS T_NAME FROM DUAL
UNION ALL SELECT '1' AS T_CD , 2 AS seq , 'ONE' AS T_NAME FROM DUAL
UNION ALL SELECT '1' AS T_CD , 3 AS seq , '하나' AS T_NAME FROM DUAL
UNION ALL SELECT '1' AS T_CD , 1 AS seq , '처음' AS T_NAME FROM DUAL
UNION ALL SELECT '2' AS T_CD , 2 AS seq , '둘' AS T_NAME FROM DUAL
UNION ALL SELECT '2' AS T_CD , 1 AS seq , 'TWO' AS T_NAME FROM DUAL
)
SELECT DECODE(T_CD,'1','1','2') AS T_CD
, COUNT(*) as CNT
, SUBSTR(XMLAGG( XMLELEMENT( X, ',' , T_NAME ) ORDER BY seq ).EXTRACT('//text()'),2 ) AS T_LIST
, WM_CONCAT(T_NAME) AS T_LIST2
FROM COL_LIST_TEMP
GROUP BY T_CD
[**] XMLAGG, XMLELEMENT 이녀석을 스탭별로 진행해보자..
# .getStringVal() : XML*** 는 xml 데이터 형식으로 가져오므로.. String 으로 변환.
WITH COL_LIST_TEMP AS (
SELECT '1' AS T_CD , 4 AS seq , '일' AS T_NAME FROM DUAL
UNION ALL SELECT '1' AS T_CD , 2 AS seq , 'ONE' AS T_NAME FROM DUAL
UNION ALL SELECT '1' AS T_CD , 3 AS seq , '하나' AS T_NAME FROM DUAL
UNION ALL SELECT '1' AS T_CD , 1 AS seq , '처음' AS T_NAME FROM DUAL
UNION ALL SELECT '2' AS T_CD , 2 AS seq , '둘' AS T_NAME FROM DUAL
UNION ALL SELECT '2' AS T_CD , 1 AS seq , 'TWO' AS T_NAME FROM DUAL
)
SELECT 'step1' as testId , T_CD
, xmlelement(a,T_NAME).getStringVal() val -- <A>TWO</A>
FROM COL_LIST_TEMP
union ALL
SELECT 'step2' as testId ,T_CD
, xmlagg(xmlelement(a,T_NAME)).getStringVal() val2 -- <A>둘</A><A>TWO</A>
FROM COL_LIST_TEMP GROUP BY T_CD
union ALL
SELECT 'step3' as testId ,T_CD
, xmlagg(xmlelement(a,T_NAME) order by T_NAME ).extract('//text()').getStringVal() val2 -- TWO둘
FROM COL_LIST_TEMP GROUP BY T_CD
union ALL
SELECT 'step4' as testId ,T_CD
, xmlagg(xmlelement(a,',' ||T_NAME) order by T_NAME ).extract('//text()').getStringVal() val2 -- ,TWO,둘
FROM COL_LIST_TEMP GROUP BY T_CD
union ALL
SELECT 'step4' as testId ,T_CD
, substr(xmlagg(xmlelement(a,',' ||T_NAME) order by T_NAME ).extract('//text()'), 2) val2 -- TWO,둘
FROM COL_LIST_TEMP GROUP BY T_CD