SQL 횡전개 응용 ORACLE 기준 응용

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