行列问题
年份 A B C D
2004 100 1 2 3
2004 150 4 5 6
2004 200 7 8 9
2005 100 1 2 3
2005 150 4 5 6
2005 200 7 8 9
2006 100 1 2 3
2006 150 4 5 6
2006 200 7 8 9
变成
年份 类别 100 150 200 ...
2004 B 1 4 7
2004 C 2 5 8
2004 D 3 6 9
2005 B 1 4 7
2005 C 2 5 8
2005 D 3 6 9
2006 B 1 4 7
2006 C 2 5 8
2006 D 3 6 9 如何实现?
[解决办法]
create table t([year] int,A int,B int,C int,D int)
insert into t select 2004,100,1,2,3
insert into t select 2004,150,4,5,6
insert into t select 2004,200,7,8,9
insert into t select 2005,100,1,2,3
insert into t select 2005,150,4,5,6
insert into t select 2005,200,7,8,9
insert into t select 2006,100,1,2,3
insert into t select 2006,150,4,5,6
insert into t select 2006,200,7,8,9
go
declare @sql varchar(8000)
select @sql= ' '
select @sql=@sql+ ',[ '+rtrim(A)+ ']=max(case A when '+rtrim(A)+ ' then v end) '
from t group by A order by A
set @sql= 'select [year],[type] '+@sql
+ ' from (select [year],A, ' 'B ' ' as [type], B as v from t '
+ ' union all '
+ ' select [year],A, ' 'C ' ' as [type], C as v from t '
+ ' union all '
+ ' select [year],A, ' 'D ' ' as [type], D as v from t) a '
+ ' group by [year],[type] '
+ ' order by [type],[year] '
exec(@sql)
go
/*
year type 100 150 200
----------- ---- ----------- ----------- -----------
2004 B 1 4 7
2005 B 1 4 7
2006 B 1 4 7
2004 C 2 5 8
2005 C 2 5 8
2006 C 2 5 8
2004 D 3 6 9
2005 D 3 6 9
2006 D 3 6 9
*/
drop table t
go
