行列问题
年份     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