: SQL高难度有关问题

高手请进: SQL高难度问题
表如下:

id name value
1 a 3
1 b 4
2 b 6
3 a 8
注: 行数不固定, 可能有很多行; name的值更不固定, 可能有多个, 绝对不是例子中列出的仅仅 a 和 b

转换成如下表结构

id a b
1 3 4
2 6
3 8

最好不用cursor, 少用临时表

[解决办法]

SQL code

create table t (id int ,name varchar(8),value int)insert into tselect 1,'a',3 union allselect 1,'b',4 union allselect 2,'b',6 union allselect 3,'a',8declare @s nvarchar(4000)set @s=''Select     @s=@s+','+quotename([name])+'=sum(case when [name]='+quotename([name],'''')+' then [value] else 0 end)'from t group by[name]exec('select [id]'+@s+' from t group by [id]')-------------------------------------id          a           b----------- ----------- -----------1           3           42           0           63           8           0(3 行受影响)
[解决办法]
动态行列转换
SQL code
declare @names varchar(max)select @names = isnull(@name,'')+',sum(case when name = ''' +name + ''' then value else 0 end) as [' + name + ']'from tabgroup by name exec('select id' + @names + 'from tabgroup by id')
[解决办法]
参照
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html