oracle 행열,열행 변환
| [오라클]행->열, 열->행 변환 /*--------------------------------------------------- * ROW => COLUMN의 변환 * COLUMN => ROW의 변환 ----------------------------------------------------*/ --------------- DEPTNO EMPNO --------------- 10 7782 10 7839 10 7934 20 7369 20 7566 20 7788 30 7499 30 7521 30 7654 ------------------------------ DEPTNO EMP1 EMP2 EMP3 ------------------------------ 10 7782 7839 7934 20 7369 7566 7788 30 7499 7521 7654 /* COLUMN => ROW 시작 */ SELECT A.DEPTNO, DECODE(C.NO, 1, A.EMP1, 2, A.EMP2, 3, A.EMP3) EMPNO FROM ( /* ROW => COLUMN 시작 */ SELECT DEPTNO, MAX(DECODE(RID, 1, EMPNO)) EMP1, MAX(DECODE(RID, 2, EMPNO)) EMP2, MAX(DECODE(RID, 3, EMPNO)) EMP3 FROM ( SELECT DEPTNO, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY EMPNO) RID, EMPNO FROM EMP ) GROUP BY DEPTNO /* ROW => COLUMN 종료 */ ) A, COPY_T C WHERE C.NO <= 3 /* COLUMN => ROW 종료 */ /*--------------------------------------------------- * CROSSTAB에서 열을 행으로 행을 열로 변환 * ROW => COLUMN, COLUMN => ROW을 한꺼번에 구현 ----------------------------------------------------*/ ------------------------------ DEPTNO EMP1 EMP2 EMP3 ------------------------------ 10 7782 7839 7934 20 7369 7566 7788 30 7499 7521 7654 ------------------------------ EMP DEPT_10 DEPT_20 DEPT_30 ------------------------------ EMP1 7782 7369 7499 EMP2 7839 7566 7521 EMP3 7934 7788 7654 SELECT DECODE(C.NO, 1, 'EMP1', 2, 'EMP2', 3, 'EMP3') EMP, MAX(DECODE(A.DEPTNO||C.NO2, '1001', A.EMP1, '1002', A.EMP2, '1003', A.EMP3)) DEPT_10, MAX(DECODE(A.DEPTNO||C.NO2, '2001', A.EMP1, '2002', A.EMP2, '2003', A.EMP3)) DEPT_20, MAX(DECODE(A.DEPTNO||C.NO2, '3001', A.EMP1, '3002', A.EMP2, '3003', A.EMP3)) DEPT_30 FROM ( /* 원래의 ROW, COLUMN구조 시작 */ SELECT DEPTNO, MAX(DECODE(RID, 1, EMPNO)) EMP1, MAX(DECODE(RID, 2, EMPNO)) EMP2, MAX(DECODE(RID, 3, EMPNO)) EMP3 FROM ( SELECT DEPTNO, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY EMPNO) RID, EMPNO FROM EMP ) GROUP BY DEPTNO /* 원래의 ROW, COLUMN구조 종료 */ ) A, COPY_T C WHERE C.NO <= 3 GROUP BY DECODE(C.NO, 1, 'EMP1', 2, 'EMP2', 3, 'EMP3') /*--------------------------------------------------- * 참고) COPY_T 의 생성 ----------------------------------------------------*/ CREATE TABLE COPY_T AS SELECT ROWNUM NO ,TO_CHAR(ROWNUM, 'FM00') NO2 FROM ALL_OBJECTS WHERE ROWNUM <= 31 CREATE UNIQUE INDEX COPY_T_IDX1 ON COPY_T(NO) CREATE UNIQUE INDEX COPY_T_IDX2 ON COPY_T(NO2) /*--------------------------------------------------- * ROW_NUMBER() 함수의 기능을 구현 => 테이블을 두번 읽기 * ROWNUM이 지원되지 않는 DBMS에서 ROWNUM 구현도 유사 ----------------------------------------------------*/ SELECT DEPTNO, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY EMPNO) RID, EMPNO FROM EMP SELECT A.DEPTNO, COUNT(*) RID, A.EMPNO FROM EMP A, EMP B WHERE A.DEPTNO = B.DEPTNO /* PARTITION BY 기능 */ AND A.EMPNO >= B.EMPNO /* ORDER BY 기능 => 반드시 UNIQUE 해야함 */ GROUP BY A.DEPTNO, A.EMPNO /* PARTITION BY,ORDER BY 기능 */ ORDER BY A.DEPTNO, A.EMPNO /* PARTITION BY,ORDER BY 기능 */ /*--------------------------------------------------- * COPY_T 테이블이 없을때 COPY_T 기능 구현방법 ----------------------------------------------------*/ SELECT NO, NO2 FROM COPY_T WHERE NO <= 5 /*------------------------------------------ * 1.USER_OBJECTS 테이블의 이용 * 최대한 가벼운 테이블 이용 * USER_OBJECTS가 가벼운지는 검증할 문제임 -------------------------------------------*/ SELECT ROWNUM NO, TO_CHAR(ROWNUM, 'FM00') NO2 FROM USER_OBJECTS WHERE ROWNUM <= 5 /*------------------------------------------ * 2.DUAL 테이블의 이용 * 복사갯수가 적을때 이용(2~3개) -------------------------------------------*/ SELECT NO, TO_CHAR(NO, 'FM00') NO2 FROM ( SELECT 1 NO FROM DUAL UNION ALL SELECT 2 FROM DUAL UNION ALL SELECT 3 FROM DUAL UNION ALL SELECT 4 FROM DUAL UNION ALL SELECT 5 FROM DUAL ) /*--------------------------------------------------- * 참고) DUAL 테이블이 없는 경우의 구현(SQL SERVER) ----------------------------------------------------*/ CREATE VIEW DUAL AS SELECT 'X' DUMMY_COL SELECT GETDATE() FROM DUAL |
