数据库查询结果如下：
业务员 年份 月份 业务量
A 2006 1 80
A 2006 2 90
A 2006 3 82
...
D 2007 9 45
想转换成如下形式：
年份 （月份）1 2 3 4 5 6 7 8 9 10 11 12
A 2006 80 90 82 。。。
A 2007 ...
..
D 2007 ... 45
------解决方案--------------------
- SQL code
create table T(业务员 varchar(100), 年份 int, 月份 int, 业务量 int)insert into T select 'A',2006, 1, 80 insert into T select 'A',2006, 2, 90 insert into T select 'A',2006, 3, 82insert into T select 'D',2007, 9, 45 select 业务员, 年份, sum(case when 月份=1 then 业务量 else null end) as [1], sum(case when 月份=2 then 业务量 else null end) as [2], sum(case when 月份=3 then 业务量 else null end) as [3], sum(case when 月份=4 then 业务量 else null end) as [4], sum(case when 月份=5 then 业务量 else null end) as [5], sum(case when 月份=6 then 业务量 else null end) as [6], sum(case when 月份=7 then 业务量 else null end) as [7], sum(case when 月份=8 then 业务量 else null end) as [8], sum(case when 月份=9 then 业务量 else null end) as [9], sum(case when 月份=10 then 业务量 else null end) as [10], sum(case when 月份=11 then 业务量 else null end) as [11], sum(case when 月份=12 then 业务量 else null end) as [12]from Tgroup by 业务员,年份order by 业务员,年份drop table T
------解决方案--------------------
- SQL code
--借用楼上的列子create table T(业务员 varchar(100), 年份 int, 月份 int, 业务量 int)insert into T select 'A',2006, 1, 80 insert into T select 'A',2006, 2, 90 insert into T select 'A',2006, 3, 82insert into T select 'D',2007, 9, 45 godeclare @s nvarchar(4000)set @s='select 业务员,年份'select @[email protected]+','+quotename(col1)+'=count( case when 年份='+rtrim(col1)+' then 1 else 0 end)'from (select 1 as col1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10 union select 11 union select 12)t1exec(@s+' from t group by 业务员,年份')业务员 年份 1 2 3 4 5 6 7 8 9 10 11 12 ---------------------------------------------------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- A 2006 3 3 3 3 3 3 3 3 3 3 3 3D 2007 1 1 1 1 1 1 1 1 1 1 1 1
------解决方案--------------------
- SQL code
--显示记录数select 业务员, 年份, [1]=count( case when 年份=1 then 1 else 0 end), [2]=count( case when 年份=2 then 1 else 0 end), [3]=count( case when 年份=3 then 1 else 0 end), [4]=count( case when 年份=4 then 1 else 0 end), [5]=count( case when 年份=5 then 1 else 0 end), [6]=count( case when 年份=6 then 1 else 0 end), [7]=count( case when 年份=7 then 1 else 0 end), [8]=count( case when 年份=8 then 1 else 0 end), [9]=count( case when 年份=9 then 1 else 0 end), [10]=count( case when 年份=10 then 1 else 0 end), [11]=count( case when 年份=11 then 1 else 0 end), [12]=count( case when 年份=12 then 1 else 0 end) from t group by 业务员,年份