关于表中行变列并且算和的问题？

[Superisboy]

有table1,table2两个表
table1:
ID Name
1 name1
2 name2
3 name3
……
table2:
ID Name Num
10 name1 1
10 name2 1
10 name2 1
10 name3 1
……
生成table3或视图:
ID name1 name2 name3
10 1 2 1
……

做了一下午也没有做出来,请赐教,谢谢!

[txlicenhe]

http://expert.csdn.net/Expert/topic/2440/2440306.xml?temp=.6941645
[交流]行列转换

[youngby]

mark

[zjcxc]

declare @s varchar(8000)
set @s=''
select @s=@s+',['+name+']=sum(case name when '''
+name+''' then num end)'
from table1
exec('select id'+@s+' from table2 group by id')

[zjcxc]

--下面是数据测试

--创建测试表
create table table1(id int,name varchar(10))
insert into table1
select 1,'name1'
union all select 2,'name2'
union all select 3,'name3'

create table table2(ID int,Name varchar(10),Num int)
insert into table2
select 10,'name1',1
union all select 10,'name2',1
union all select 10,'name2',1
union all select 10,'name3',1

--查询处理
declare @s varchar(8000)
set @s=''
select @s=@s+',['+name+']=sum(case name when '''
+name+''' then num end)'
from table1
exec('select id'+@s+' from table2 group by id')

go
--删除测试环境
drop table table1,table2

/*--测试结果

id name1 name2 name3
----------- ----------- ----------- -----------
10 1 2 1

--*/

[zjcxc]

如果你table1中的数据固定,也可以直接用:

select id
,[name1]=sum(case name when 'name1' then num end)
,[name2]=sum(case name when 'name2' then num end)
,[name3]=sum(case name when 'name3' then num end)
from table2 group by id