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

var viewer_image_url = "http://blogimgs.naver.com/blog20/blog/layout_photo/viewer/"; var photo = new PhotoLayer(parent.parent.parent); photo.Initialized(); window.onunload = function() { photo.oPhotoFrame.doFrameMainClose(); }.bind(this);

출처 : http://blog.naver.com/PostView.nhn?blogId=mcm27xx&logNo=60157926523 

 

/* Formatted on 2013-01-29 오후 5:12:38 (QP5 v5.136.908.31019) */
  SELECT DECODE(LVL, 1, THISYEAR_END_DT, 2, LASTYEAR_END_DT, TWOYEARSAGO_END_DT) AS END_DT
    FROM (SELECT '(' || NVL(TO_CHAR(END_DT, 'YYYY-MM-DD'), 'N/A') || ')' AS THISYEAR_END_DT
                ,(SELECT TO_CHAR('(' || NVL(TO_CHAR(END_DT, 'YYYY-MM-DD'), 'N/A') || ')')
                    FROM K_LEADERSHIPDATENEW
                   WHERE LEADERSHIP_SEQ = 5)
                     AS LASTYEAR_END_DT
                ,(SELECT  TO_CHAR('(' || NVL(TO_CHAR(END_DT, 'YYYY-MM-DD'), 'N/A') || ')')
                    FROM K_LEADERSHIPDATENEW
                   WHERE LEADERSHIP_SEQ = 10)
                     AS TWOYEARSAGO_END_DT
                ,B.LVL
            FROM K_LEADERSHIPDATENEW A
                ,(    SELECT LEVEL AS LVL
                        FROM DUAL
                  CONNECT BY LEVEL < 4) B
           WHERE LEADERSHIP_SEQ = 6)
GROUP BY DECODE(LVL, 1, THISYEAR_END_DT, 2, LASTYEAR_END_DT, TWOYEARSAGO_END_DT)
ORDER BY MAX(LVL)