여러 행을 하나의 컬럼으로 합치기

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