请问一个行列转换的老有关问题

请教一个行列转换的老问题
请教一个行列转换的老问题

我有一个这样的表

id price1 price2 bigclass smallclass year
1 10 20 big1 small1 2011
2 12 12 big1 small2 2011
3 6 20 big2 small3 2011
4 36 25 big3 small4 2011


转换后想变成这个样，主要就是大小类和价格
大小类都是动态，可添加，删除

year small1 small1 small2 small2 small3 small3 small4 small4
2011 10 20 12 12 6 20 36 25


非常感谢


[解决办法]

SQL code

use Tempdbgo--> -->  if not object_id(N'Tempdb..#T') is null    drop table #TGoCreate table #T([id] int,[price1] int,[price2] int,[bigclass] nvarchar(4),[smallclass] nvarchar(6),[year] int)Insert #Tselect 1,10,20,N'big1',N'small1','2011' union allselect 2,12,12,N'big1',N'small2','2011' union allselect 3,6,20,N'big2',N'small3','2011' union allselect 4,36,25,N'big3',N'small4','2011'Godeclare @s nvarchar(4000)set @s=''Select     @s=@s+N','+quotename(smallclass+'price1')+N'=max(case when smallclass=N'+quotename(smallclass,'''')+N' then price1 else '''' end),'                +quotename(smallclass+'price2')+N'=max(case when smallclass=N'+quotename(smallclass,'''')+N' then [price2] else '''' end)'from #T group by smallclass--顯示生成語句print N'select year'+@s+N' from #T group by year'exec(N'select year'+@s+N' from #T group by year')go/*year    small1price1    small1price2    small2price1    small2price2    small3price1    small3price2    small4price1    small4price22011    10    20    12    12    6    20    36    25*/