가로집계

2011-07-28 19:32:22

---

SQL 4 Oracle 오라클용 sql 실무

▒ 가로집계 동일인 취득형태별 취득일자 구간별 점수 가로 집계

 

select 고객ID,고객명,취득형태,
      ,sum(decode(취득구간,'m03',to_number(점수),0)) n03
      ,sum(decode(취득구간,'m06',to_number(점수),0)) n06
      ,sum(decode(취득구간,'m12',to_number(점수),0)) n12
      ,sum(decode(취득구간,'m18',to_number(점수),0)) n18
      ,sum(decode(취득구간,'m24',to_number(점수),0)) n24
      ,sum(decode(취득구간,'m36',to_number(점수),0)) n36
      ,sum(decode(취득구간,'m48',to_number(점수 ),0)) n48
      ,sum(decode(취득구간,'m60',to_number(점수),0)) n60
      ,sum(decode(취득구간,'m99',to_number(점수),0)) n99
from (
       select a.고객ID,a.고객명,a.취득형태,
          (case when (trunc(months_between(to_char(sysdate,'yyyymmdd'),a.취득일자)) <=  3) then 'm03'
                   when (trunc(months_between(to_char(sysdate,'yyyymmdd'),a.취득일자)) <=  6) then 'm06'
                   when (trunc(months_between(to_char(sysdate,'yyyymmdd'),a.취득일자)) <= 12) then 'm12'
                   when (trunc(months_between(to_char(sysdate,'yyyymmdd'),a.취득일자)) <= 18) then 'm18'
                   when (trunc(months_between(to_char(sysdate,'yyyymmdd'),a.취득일자)) <= 24) then 'm24'
                   when (trunc(months_between(to_char(sysdate,'yyyymmdd'),a.취득일자)) <= 36) then 'm36'
                   when (trunc(months_between(to_char(sysdate,'yyyymmdd'),a.취득일자)) <= 48) then 'm48'
                   when (trunc(months_between(to_char(sysdate,'yyyymmdd'),a.취득일자)) <= 60) then 'm60'
                   else 'm99'
           end) 취득구간,
           점수 
        from 목적_Table a
       where  a.고객상태 = '활동중'
)
group by 고객ID,고객명,취득형태