[Oracle] 테이블 정렬 (PIVOT)

테이블 정렬을 하고 싶을때 사용법

 

기존 테이블 형식

순서	DNUM	DNAME
1	1	TEST1
2	1	TEST2
3	1	TEST3
4	2	TEST4
5	2	TEST5
6	2	TEST6

---

1번 사용법

 

WITH 
T(DNUM, DNAME) AS (
       SELECT 1, 'TEST1' FROM DUAL UNION ALL
       SELECT 1, 'TEST2' FROM DUAL UNION ALL
       SELECT 1, 'TEST3' FROM DUAL UNION ALL
       SELECT 2, 'TEST4' FROM DUAL UNION ALL
       SELECT 2, 'TEST5' FROM DUAL UNION ALL
       SELECT 2, 'TEST6' FROM DUAL)
SELECT MAX(DECODE(DNUM, 1, DNAME)) "1",
       MAX(DECODE(DNUM, 2, DNAME)) "2"
FROM (SELECT DNUM, DNAME, ROW_NUMBER() OVER (PARTITION BY DNUM ORDER BY ROWNUM) RN FROM T)
GROUP BY RN
ORDER BY RN

 

순서	1	2
1	TEST1	TEST4
2	TEST2	TEST5
3	TEST3	TEST6

 

---

2번 사용법

 

WITH 
T(DNUM, SEQ, DNAME) AS (
       SELECT 1, 1, 'TEST1' FROM DUAL UNION ALL
       SELECT 1, 2, 'TEST2' FROM DUAL UNION ALL
       SELECT 1, 3, 'TEST3' FROM DUAL UNION ALL
       SELECT 2, 1, 'TEST4' FROM DUAL UNION ALL
       SELECT 2, 2, 'TEST5' FROM DUAL UNION ALL
       SELECT 2, 3, 'TEST6' FROM DUAL)
SELECT * FROM T
       PIVOT (MIN(DNAME) FOR DNUM IN (1,2))
       ORDER BY SEQ

 

순서	SEQ	1	2
1	1	TEST1	TEST4
2	2	TEST2	TEST5
3	3	TEST3	TEST6

 

 

이렇게 사용 가능 하다