请教一个行列转换的老问题
我有一个这样的表
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 @[email protected]+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 [email protected]+N' from #T group by year'exec(N'select [email protected]+N' from #T group by year')go/*year small1price1 small1price2 small2price1 small2price2 small3price1 small3price2 small4price1 small4price22011 10 20 12 12 6 20 36 25*/