select
作者 =creator,
数量 =sum(case ResType when 1 then [count] else 0 end),
数据量=sum(case ResType when 1 then [size] else 0 end),
数量 =sum(case ResType when 2 then [count] else 0 end),
数据量=sum(case ResType when 2 then [size] else 0 end),
数量 =sum(case ResType when 3 then [count] else 0 end),
数据量=sum(case ResType when 3 then [size] else 0 end),
数量 =sum(case ResType when 4 then [count] else 0 end),
数据量=sum(case ResType when 4 then [size] else 0 end)
from
表
group by
creator
作者 =creator,
数量 =sum(case ResType when 1 then [count] else 0 end),
数据量=sum(case ResType when 1 then [size] else 0 end),
数量 =sum(case ResType when 2 then [count] else 0 end),
数据量=sum(case ResType when 2 then [size] else 0 end),
数量 =sum(case ResType when 3 then [count] else 0 end),
数据量=sum(case ResType when 3 then [size] else 0 end),
数量 =sum(case ResType when 4 then [count] else 0 end),
数据量=sum(case ResType when 4 then [size] else 0 end)
from
表
group by
creator
作者 =creator,
数量 =sum(case ResType when 1 then [count] else 0 end),
数据量=sum(case ResType when 1 then [size] else 0 end),
数量 =sum(case ResType when 2 then [count] else 0 end),
数据量=sum(case ResType when 2 then [size] else 0 end),
数量 =sum(case ResType when 3 then [count] else 0 end),
数据量=sum(case ResType when 3 then [size] else 0 end),
数量 =sum(case ResType when 4 then [count] else 0 end),
数据量=sum(case ResType when 4 then [size] else 0 end)
from
表
group by
creator
另外x轴也是变化的列.就是说第一个表中可能不是creator 和ResType字段
又该如何简单的变化??
go--插入数据
insert into t3
select 2,993152,1,1 union all
select 1,6598,1,2 union all
select 1,878,1,3 declare @s varchar(8000)set @s = ''
select @s = @s +',数量 = sum(case restype when '+convert(varchar(2),b.restype)+' then [count] else 0 end ),数据量= sum(case restype when '+convert(varchar(2),b.restype)+' then [size] else 0 end )'
from (select distinct restype from t3) b
exec ('select creator'+@s+' from t3 group by creator')drop table t3/*
creator 数量 数据量 数量 数据量 数量 数据量
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 2 993152 1 6598 1 878
*/
go--插入数据
insert into t3
select 2,993152,1,1 union all
select 1,6598,1,2 union all
select 1,878,1,3 union all
select 4,1234,1,4 declare @s varchar(8000)set @s = ''
select @s = @s +',数量 = sum(case restype when '+convert(varchar(2),b.restype)+' then [count] else 0 end ),数据量= sum(case restype when '+convert(varchar(2),b.restype)+' then [size] else 0 end )'
from (select distinct restype from t3) b
exec ('select creator'+@s+' from t3 group by creator')drop table t3/*
creator 数量 数据量 数量 数据量 数量 数据量 数量 数据量
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 2 993152 1 6598 1 878 4 1234
*/