SQL 횡전개 응용 ORACLE 기준 응용

IT/SQL

SQL 횡전개 응용 ORACLE 기준 응용

zolla 2011. 4. 15. 13:32

WITH TBDWMERSMBMORCD AS
(
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-1),'YYYYMM') ACPT_YM, 20 MB_NO
    FROM   DUAL
    UNION ALL
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-2),'YYYYMM') ACPT_YM, 20 MB_NO
    FROM   DUAL
    UNION ALL
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-3),'YYYYMM') ACPT_YM, 20 MB_NO
    FROM   DUAL
    UNION ALL
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-4),'YYYYMM') ACPT_YM, 20 MB_NO
    FROM   DUAL
    UNION ALL
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-5),'YYYYMM') ACPT_YM, 20 MB_NO
    FROM   DUAL
    UNION ALL
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-6),'YYYYMM') ACPT_YM, 20 MB_NO
    FROM   DUAL
    UNION ALL
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-1),'YYYYMM') ACPT_YM, 10 MB_NO
    FROM   DUAL
    UNION ALL
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-2),'YYYYMM') ACPT_YM, 10 MB_NO
    FROM   DUAL
    UNION ALL
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-3),'YYYYMM') ACPT_YM, 10 MB_NO
    FROM   DUAL
    UNION ALL
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-4),'YYYYMM') ACPT_YM, 10 MB_NO
    FROM   DUAL
    UNION ALL
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-5),'YYYYMM') ACPT_YM, 10 MB_NO
    FROM   DUAL
    UNION ALL
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-6),'YYYYMM') ACPT_YM, 10 MB_NO
    FROM   DUAL
)
SELECT 
    NVL(MB_NO, 'TOTAL') MB_NO,
    NVL(LV, 'TOTAL') LV,
    SALE_AMT_5, SALE_AMT_4, SALE_AMT_3, SALE_AMT_2, SALE_AMT_1, SALE_AMT
FROM (
    SELECT 
        MB_NO||'' MB_NO,LV||'' LV,
        SUM( CASE WHEN TO_CHAR(add_months(sysdate,-5),'yyyymm') = ACPT_YM THEN DECODE(LV,1,LUMP_NSALE_AMT,2,INS_NSALE_AMT,3,SPAY_AMT) END ) SALE_AMT_5, 
        SUM( CASE WHEN TO_CHAR(add_months(sysdate,-4),'yyyymm') = ACPT_YM THEN DECODE(LV,1,LUMP_NSALE_AMT,2,INS_NSALE_AMT,3,SPAY_AMT) END ) SALE_AMT_4, 
        SUM( CASE WHEN TO_CHAR(add_months(sysdate,-3),'yyyymm') = ACPT_YM THEN DECODE(LV,1,LUMP_NSALE_AMT,2,INS_NSALE_AMT,3,SPAY_AMT) END ) SALE_AMT_3, 
        SUM( CASE WHEN TO_CHAR(add_months(sysdate,-2),'yyyymm') = ACPT_YM THEN DECODE(LV,1,LUMP_NSALE_AMT,2,INS_NSALE_AMT,3,SPAY_AMT) END ) SALE_AMT_2, 
        SUM( CASE WHEN TO_CHAR(add_months(sysdate,-1),'yyyymm') = ACPT_YM THEN DECODE(LV,1,LUMP_NSALE_AMT,2,INS_NSALE_AMT,3,SPAY_AMT) END ) SALE_AMT_1,
        SUM( CASE WHEN TO_CHAR(sysdate,'yyyymm') = ACPT_YM THEN DECODE(LV,1,LUMP_NSALE_AMT,2,INS_NSALE_AMT,3,SPAY_AMT) END ) SALE_AMT
    FROM   TBDWMERSMBMORCD, (SELECT level lv FROM dual CONNECT BY level <= 3)
    --WHERE to_date(ACPT_YM,'yyyymm') < to_date(TO_CHAR(sysdate,'yyyymm'),'yyyymm')
    WHERE ACPT_YM IN ( TO_CHAR(add_months(sysdate,-5),'yyyymm'), 
                       TO_CHAR(add_months(sysdate,-4),'yyyymm'), 
                       TO_CHAR(add_months(sysdate,-3),'yyyymm'), 
                       TO_CHAR(add_months(sysdate,-2),'yyyymm'), 
                       TO_CHAR(add_months(sysdate,-1),'yyyymm'),
                       TO_CHAR(sysdate,'yyyymm') )
                           --AND to_date(ACPT_YM,'yyyymm') > add_months(sysdate,-6)
    GROUP BY CUBE (MB_NO,LV) --, (LV) )
    --AND MER_MGMT_NO = '700000014'
)
WHERE MB_NO IS NULL OR MB_NO = 20
ORDER BY 1, 2