Pivot 多列转行的问题
各位亲,
如果是单列转行用Pivot 好解决,但是用pivot如何达到多列转成行的效果?
ID,Item1,Number1,Number2
1,A,10,1
1,B,5,3
1,A,3,2
2,A,5,3
2,B,1,2
2,B,2,2
效果
ID,A(Number1),A(Number2),B(Number1),B(Number2)
1,13,3,5,3
2,5,3,3,4
非常感谢帮忙,在线等!
------解决方案--------------------
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+item1+'=sum(case when [item1]='+quotename(item1,'''')+' then no1 else 0 end)'+','
+quotename(item1 )+'=sum(case when [item1]='+quotename(item1,'''')+' then no2 else 0 end)'
from t1 group by item1
print @s
exec('select id'+@s+' from t1 gro