转载

Excel实现动态行转列(数据透视表)

固定行转列比较容易实现.
但是动态行转列的需求,用SQL都难以实现,要不然就是超级复杂。
不过很多第三方工具都已经提供了很好的支持,不一定非得用SQL实现.比如帆软报表和Excel。

最近接到一个比较复杂的查询,并且要求动态行转列.

查询的SQL如下

select type,ts,cn,cast(val as DECIMAL) val from (
select
starttime,'发布' type,base.ts,base.cn,ifnull(t1.val,0) val
from
(
select
t1.*,
startdate+ interval id-1 hour starttime,
startdate+ interval id hour endtime,
date_format(startdate+ interval id-1 hour,'%m%d%H') ts,
vars.*
from nums ,(select ${starttime} startdate,${endtime} enddate) vars,
(
select 'mvbox_user.user_otherinfo' busidatatype,1 type,'内容审核.个人资料' cn union all
select 'mvbox_user.user_baseinfo',1,'内容审核.个人喜好' union all
select 'photo_album',1,'内容审核.相册信息' union all
select 'photo_list',1,'内容审核.图片信息' union all
select 'music_original',1,'内容审核.原唱信息' union all
select 'music_cover',1,'内容审核.翻唱信息' union all
select 'music_accompany',1,'内容审核.伴奏信息' union all
select 'music_album',1,'内容审核.音乐专辑' union all
select 'music_video',1,'内容审核.视频信息' union all
select 'blog_album',1,'内容审核.日志与文章' union all
select 'mvbox_user.user_baseinfo',2,'内容审核.MVBOX头像审核'
) t1
where id<= TIMESTAMPDIFF(hour,startdate,enddate)
order by busidatatype,type,starttime
) base left join
(
select busidatatype,type,date_format(createtime,'%m%d%H') ts,count(*) val from audit_obj_detail
where createtime>=${starttime} and createtime<${endtime}
and busitype = 'mvbox'
group by busidatatype,type,date_format(createtime,'%m%d%H')
) t1 on (base.busidatatype=t1.busidatatype and base.type=t1.type and base.ts=t1.ts)
group by base.busidatatype,base.type,base.ts,base.cn
union all
select
starttime, '审核' type,base.ts,base.cn,ifnull(t1.val,0) val
from
(
select
t1.*,
startdate+ interval id-1 hour starttime,
startdate+ interval id hour endtime,
date_format(startdate+ interval id-1 hour,'%m%d%H') ts,
vars.*
from nums ,(select ${starttime} startdate,${endtime} enddate) vars,
(
select 'mvbox_user.user_otherinfo' busidatatype,1 type,'内容审核.个人资料' cn union all
select 'mvbox_user.user_baseinfo',1,'内容审核.个人喜好' union all
select 'photo_album',1,'内容审核.相册信息' union all
select 'photo_list',1,'内容审核.图片信息' union all
select 'music_original',1,'内容审核.原唱信息' union all
select 'music_cover',1,'内容审核.翻唱信息' union all
select 'music_accompany',1,'内容审核.伴奏信息' union all
select 'music_album',1,'内容审核.音乐专辑' union all
select 'music_video',1,'内容审核.视频信息' union all
select 'blog_album',1,'内容审核.日志与文章' union all
select 'mvbox_user.user_baseinfo',2,'内容审核.MVBOX头像审核'
) t1
where id<= TIMESTAMPDIFF(hour,startdate,enddate)
order by busidatatype,type,starttime
) base left join
(
select busidatatype,type,date_format(AuditTime,'%m%d%H') ts,count(*) val from audit_obj_detail
where AuditTime>=${starttime} and AuditTime<${endtime}
and busitype = 'mvbox'
group by busidatatype,type,date_format(AuditTime,'%m%d%H')
) t1 on (base.busidatatype=t1.busidatatype and base.type=t1.type and base.ts=t1.ts)
group by base.busidatatype,base.type,base.ts,base.cn
) t1 ;