求教一个纵表转横表的问题

Question

老问题了。写function,用游标实现搜索一下以前的

Answer 1

select 
 a.name,
 to_char(a.time,'yyyy-mm') t_time,
 decode(sum(decode(a.pub_name,pub1,1,0)),0,null,sum(decode(a.pub_name,pub1,1,0))) pub1,
 decode(sum(decode(a.pub_name,pub2,1,0)),0,null,sum(decode(a.pub_name,pub2,1,0))) pub2, 
 decode(sum(decode(a.pub_name,pub3,1,0)),0,null,sum(decode(a.pub_name,pub3,1,0))) pub3,
 decode(sum(decode(a.pub_name,pub4,1,0)),0,null,sum(decode(a.pub_name,pub4,1,0))) pub4,
 decode(sum(decode(a.pub_name,pub5,1,0)),0,null,sum(decode(a.pub_name,pub5,1,0))) pub5,
 decode(sum(decode(a.pub_name,pub6,1,0)),0,null,sum(decode(a.pub_name,pub6,1,0))) pub6,
 decode(sum(decode(a.pub_name,pub7,1,0)),o,null,sum(decode(a.pub_name,pub7,1,0))) pub7from 
    table a
group by 
    a.name
    to_char(a.time,'yyyy-mm')

Answer 2

提示非单分组函数；
select 
       c.f_name,
       replace(to_char(b.f_pub_date,'YYYY-MM'),'-','.'),
       decode(b.f_pub_id,1,sum(decode(b.f_pub_id,1,count(*),0)),0) f_pub_first,
       decode(b.f_pub_id,2,sum(decode(b.f_pub_id,2,count(*),0)),0) f_pub_first,
       decode(b.f_pub_id,3,sum(decode(b.f_pub_id,3,count(*),0)),0) f_pub_first,
       decode(b.f_pub_id,4,sum(decode(b.f_pub_id,4,count(*),0)),0) f_pub_first,
       decode(b.f_pub_id,5,sum(decode(b.f_pub_id,5,count(*),0)),0) f_pub_first,
       decode(b.f_pub_id,6,sum(decode(b.f_pub_id,6,count(*),0)),0) f_pub_first,
       decode(b.f_pub_id,6,sum(decode(b.f_pub_id,7,count(*),0)),0) f_pub_first
      
  from
      t_ynszf_infostat_data a,
      t_ynszf_infostat_datapub b,
      t_ynszf_infostat_dept c
      
 where 
       a.pk_id = b.f_data_id
   and c.pk_id = a.f_dept_id
 group by c.f_name,replace(to_char(b.f_pub_date,'YYYY-MM'),'-','.')

Answer 3

count(*) 不能这么用的.  把它调整成子查询.