各位亲，
如果是单列转行用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 group by [id]')
------解决方案--------------------
PIVOT是有局限性的 多行就不好做了
还是建议用MAX(CASE WHEN 。。。)
------解决方案--------------------
create table ch
(ID int,Item1 varchar(5),Number1 int,Number2 int)
insert into ch
select 1,'A',10,1 union all
select 1,'B',5,3 union all
select 1,'A',3,2 union all
select 2,'A',5,3 union all
select 2,'B',1,2 union all
select 2,'B',2,2
select ID,[A(Number1)],[A(Number2)],[B(Number1)],[B(Number2)]
from
(select ID,Item1+'('+c+')' 'c',v
from
(select ID,Item1,sum(Number1) 'Number1',sum(Number2) 'Number2'
from ch
group by ID,Item1) a
unpivot(v for c in([Number1],[Number2])) u) b
pivot(max(v) for c in([A(Number1)],[A(Number2)],[B(Number1)],[B(Number2)])) p
/*
ID A(Number1) A(Number2) B(Number1) B(Number2)
----------- ----------- ----------- ----------- -----------
1 13 3 5 3
2 5 3 3 4
(2 row(s) affected)
*/