年份 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 如何实现?
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 如何实现?
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
godeclare @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 Aset @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