我的行列转换问题
数据库查询结果如下：

业务员 年份 月份 业务量
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 @s=@s+','+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 业务员,年份 
 
[解决办法]
SQL code
create table o(业务员 varchar(50),年份 int,月份 int,业务量 int)insert into o select 'A',2006,1,80insert into o select 'A',2006,2,90insert into o select 'A',2006,3,82insert into o select 'A',2006,4,78insert into o select 'A',2006,5,79insert into o select 'A',2006,6,81insert into o select 'A',2006,7,82insert into o select 'A',2006,8,83insert into o select 'A',2007,1,83insert into o select 'B',2006,1,83insert into o select 'c',2007,12,11declare @sql varchar(1000)set @sql='select 业务员,年份'select @sql=@sql+',['+cast(月份 as varchar)+']=sum(case cast(月份 as varchar) when '''+ cast(月份 as varchar) +''' then 业务量 else 0 end)' from (select distinct(月份) from o)aprint @sqlset @sql=@sql+' from o group by 业务员,年份'exec(@sql)
[解决办法]
SQL code
SELECT 业务员,年份 ,   SUM(CASE 月份 WHEN 1 THEN 业务量 ELSE 0 END) AS '1月' ,  SUM(CASE 月份 WHEN 2 THEN 业务量 ELSE 0 END) AS '2月' ,  SUM(CASE 月份 WHEN 3 THEN 业务量 ELSE 0 END) AS '3月' ,  SUM(CASE 月份 WHEN 4 THEN 业务量 ELSE 0 END) AS '4月' ,  SUM(CASE 月份 WHEN 5 THEN 业务量 ELSE 0 END) AS '5月' ,  SUM(CASE 月份 WHEN 6 THEN 业务量 ELSE 0 END) AS '6月' ,  SUM(CASE 月份 WHEN 7 THEN 业务量 ELSE 0 END) AS '7月' ,  SUM(CASE 月份 WHEN 8 THEN 业务量 ELSE 0 END) AS '8月' ,  SUM(CASE 月份 WHEN 9 THEN 业务量 ELSE 0 END) AS '9月' ,  SUM(CASE 月份 WHEN 10 THEN 业务量 ELSE 0 END) AS '10月' ,  SUM(CASE 月份 WHEN 11 THEN 业务量 ELSE 0 END) AS '11月' ,  SUM(CASE 月份 WHEN 12 THEN 业务量 ELSE 0 END) AS '12月' FROM tbGROUP BY 业务员,年份
[解决办法]
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 top 12 col=identity(int,1,1) into #d from sysobjects a,sysobjects bdeclare @s varchar(8000)set @s = 'select  case when grouping(业务员)=1 then '''+'总计'' else 业务员 end as 业务员 ,年份'select @s=@s+','+quotename(cast(col as varchar)+'月')+'=sum(case when 月份='+cast(col as varchar)+' then 业务量 end)' from t a right join #d b on a.月份=b.col print @sexec(@s+' from t group by 业务员,年份 with rollup having (grouping(业务员)=0 and grouping(年份)=0) or(grouping(业务员)=1 and grouping(年份)=1)')