오라클 피벗(Pivot)

오라클 버전별 피벗 기능 입니다.

 

▶ 오라클 버전 확인하기

https://meyouus.tistory.com/184

오라클 버전 확인 하기

 

 

오라클 9i

 

▶ XMLAGG, EXTRACT ~ GROUP BY 이용

행(세로) ---> 가로(열) 변환

 

WITH SOURCE
AS (
SELECT 'SAMSUNG' AS Company, 'Galaxy 20' AS Phone FROM DUAL UNION ALL
SELECT 'SAMSUNG' AS Company, 'Galaxy Fold' AS Phone FROM DUAL UNION ALL
SELECT 'SAMSUNG' AS Company, 'Galaxy Z Flip' AS Phone FROM DUAL UNION ALL
SELECT 'APPLE' AS Company, 'iPhone 11' AS Phone FROM DUAL UNION ALL
SELECT 'APPLE' AS Company, 'iPhone 10' AS Phone FROM DUAL UNION ALL
SELECT 'LG' AS Company, 'V60' AS Phone FROM DUAL UNION ALL
SELECT 'LG' AS Company, 'V50' AS Phone FROM DUAL
)
SELECT COMPANY , SUBSTR(XMLAGG(XMLELEMENT(A,', ' || PHONE ) ORDER BY PHONE).EXTRACT('//text()'), 2) PHONE
FROM SOURCE A
GROUP BY COMPANY
ORDER BY COMPANY DESC;

 

☆ SUBSTR(XMLAGG(XMLELEMENT(A,', ' || PHONE ) ORDER BY PHONE).EXTRACT('//text()'), 2)

', ' : 구분자

 

-- xmlelement는 주어진 태그로 값을 감싸 하나의 xml 엘리먼트를 만들어 준다
-- 따로 문자열 표시를 하지 않기 때문에 예약어의 경우 에러가 난다. 
   이때 '"로 감싸주면 해결 가능. 
-- xmlagg나 xmlelement를 사용하여 나온 결과는 xml형을 가지게 됩니다. 
   이걸 substr을 쓰면 문자열형식으로 변환, 
   substr을 사용하지 않고 문자열 형식으로 받고 싶을 때는 
   .getStringVal()을 사용하면 된다.
-- String Type은 4000Bytes 이상이면 getCLOBval()를 이용합니다.
   
   
   자료출처 : https://goddaehee.tistory.com/57?category=275012

 

 

 

오라클 10g

 

▶ WM_CONCAT (컬럼명) : 정렬 안됨, 오라클 11g 사용시 에러 발생 ==> LISTAGG로 해결

 

WITH SOURCE
AS (
SELECT 'SAMSUNG' AS Company, 'Galaxy 20' AS Phone FROM DUAL UNION ALL
SELECT 'SAMSUNG' AS Company, 'Galaxy Fold' AS Phone FROM DUAL UNION ALL
SELECT 'SAMSUNG' AS Company, 'Galaxy Z Flip' AS Phone FROM DUAL UNION ALL
SELECT 'APPLE' AS Company, 'iPhone 11' AS Phone FROM DUAL UNION ALL
SELECT 'APPLE' AS Company, 'iPhone 10' AS Phone FROM DUAL UNION ALL
SELECT 'LG' AS Company, 'V60' AS Phone FROM DUAL UNION ALL
SELECT 'LG' AS Company, 'V50' AS Phone FROM DUAL
)
SELECT COMPANY , WM_CONCAT(PHONE ) AS PHONE
FROM SOURCE
GROUP BY COMPANY
ORDER BY COMPANY DESC;

 

 

 

오라클 11g

 

▶LISTAGG : 정렬 가능

-- listagg (컬럼명, '구분기호') within group (order by 정렬기준컬럼)

 

WITH SOURCE
AS (
SELECT 'SAMSUNG' AS Company, 'Galaxy 20' AS Phone FROM DUAL UNION ALL
SELECT 'SAMSUNG' AS Company, 'Galaxy Fold' AS Phone FROM DUAL UNION ALL
SELECT 'SAMSUNG' AS Company, 'Galaxy Z Flip' AS Phone FROM DUAL UNION ALL
SELECT 'APPLE' AS Company, 'iPhone 11' AS Phone FROM DUAL UNION ALL
SELECT 'APPLE' AS Company, 'iPhone 10' AS Phone FROM DUAL UNION ALL
SELECT 'LG' AS Company, 'V60' AS Phone FROM DUAL UNION ALL
SELECT 'LG' AS Company, 'V50' AS Phone FROM DUAL
)
SELECT COMPANY, LISTAGG(PHONE,',') WITHIN GROUP(ORDER BY PHONE) AS PHONE
FROM SOURCE
GROUP BY COMPANY
ORDER BY COMPANY DESC;

 

 

※ WM_CONCAT, LISTAGG는 Varchar2형으로 4000을 넘는 경우 데이터가 잘린다.