动态行转列

因狗血的报表展现需求 需要根据每天的新增用户数量 选取前5个渠道.然后把该渠道当月每一天的新增用户数据绘制在图表上

X轴是 日期 Y轴是新增用户数量 画5条线. 这5条线是动态的,每天不同的线条.

数据经过后台统计后形成表 : statedate,channle_id,channle_name,New_user_num.

一般行转列方法:

select statedate,

sum(case when channle_name='超市'  then new_user_num end )as '超市',

sum(case when channle_name='专卖店'  then new_user_num end )as '专卖店'  ,

sum(case when channle_name='合作店'  then new_user_num end )as '合作店'  ,

sum(case when channle_name='网络商城 then new_user_num end) as '网络商城',  

sum(case when channle_name='直销'       then new_user_num end )as '直销' 

from table_name

group by staedate;

 

可channle_name='超市' 值是变动的呀!

具体这样做:

with resu as
(
    select statedate,substr(to_char(statedate),7,2) as statedate_v, a.columnid,a.new_user_num,t.Name,rownum as row_id
    FROM   OSS_CONTENT_RUN_SUBS_COLUM_NEW a
    left join  ngoss_pdl.vw_dim_ss_column_base_info t  on a.columnid=t.ColumnID
    WHERE   a.statedate in
    (SELECT d.date_id FROM ngoss_edw.dim_date d WHERE to_number(substr(to_char(d.date_id), 1, 6)) = to_number(to_char(sysdate-1,'yyyymm')))
    and a.columnid in
    (
        select columnid
        from
        (
          select columnid
          from OSS_COLUM_NEW
          where statedate = 20120508
          order by new_user_num desc
        )
        where rownum<=5   
    )
    and a.statedate >=to_number(to_char(trunc(sysdate-1,'mm'),'yyyymmdd')) and a.statedate <= to_number(to_char(sysdate,'yyyymmdd'))
    order by new_user_num desc

)
select  statedate_v,
  sum( case when s.name=(select name from resu where row_id=5) then new_user_num end)   as  top1
  sum  ( case when s.name=(select name from resu where row_id=4) then new_user_num end) as  TOP2,
  sum  ( case when s.name=(select name from resu where row_id=3) then new_user_num end) as  TOP3,
  sum  ( case when s.name=(select name from resu where row_id=2) then new_user_num end) as  TOP4,
  sum  ( case when s.name=(select name from resu where row_id=1) then new_user_num end) as  TOP5       
  from resu s
group by statedate_v

 

---------------------------------- 改进----------------------

with resu as
(
    select statedate,substr(to_char(statedate),7,2) as statedate_v, a.columnid,a.new_user_num,t.Name, rw as rankid
    FROM   OSS_COLUM_NEW a
    left join  ngoss_pdl.vw_dim_ss_column_base_info t  on a.columnid=t.ColumnID
    inner join
    (
        select columnid,rownum as rw
        from
        (
          select columnid
          from OSS_COLUM_NEW  where statedate =  to_number(to_char(sysdate-1,'yyyymmdd'))
          order by new_user_num desc
        )
        where rownum<=5   
    )c  on a.columnid=c.columnid
    WHERE   a.statedate in
    (
        SELECT d.date_id FROM ngoss_edw.dim_date d
        WHERE to_number(substr(to_char(d.date_id), 1, 6)) = to_number(to_char(sysdate-1,'yyyymm'))
    )   
   
    and a.statedate >=to_number(to_char(trunc(sysdate-1,'mm'),'yyyymmdd')) and a.statedate <= to_number(to_char(sysdate-1,'yyyymmdd'))
    order by columnid,statedate

)
select  statedate_v,
  sum  ( case when s.name=(select name from resu R where RANKID=1 and rownum=1) then new_user_num end) as  TOP1,
  sum  ( case when s.name=(select name from resu r where RANKID=2 and rownum=1) then new_user_num end) as  TOP2,
  sum  ( case when s.name=(select name from resu r where RANKID=3 and rownum=1) then new_user_num end) as  TOP3,
  sum  ( case when s.name=(select name from resu r where RANKID=4 and rownum=1) then new_user_num end) as  TOP4,
  sum  ( case when s.name=(select name from resu r where RANKID=5 and rownum=1) then new_user_num end) as  TOP5       
  from resu s
group by statedate_v
order by statedate_v

 解决返回多行的问题:select name from resu R where RANKID=1 and rownum=1