日期:2014-05-18 浏览次数:20646 次
--> 测试数据:[test] if object_id('[test]') is not null drop table [test] create table [test]([name] varchar(4),[score] int,[test] int) insert [test] select '张三',90,1 union all select '张三',59,2 union all select '张三',39,3 declare @str varchar(max) set @str='' select @str=@str+','+'[test'+LTRIM(test)+']=max(case when [test]='+ltrim(test)+ ' then score else 0 end)' from test group by test exec('select name'+@str+' from test group by name') /* name test1 test2 test3 张三 90 59 39 */
------解决方案--------------------
结果用一个字段可行?
declare @t table ([name] varchar(20),score int,testNo int) insert into @t select '张三',90, 1 union all select '张三',59, 2 union all select '张三',39, 3 union all select '李四',75, 1 select [name]+ (select ','+cast(score as varchar(20)) from @t as X where X.[name]=Y.[name] order by testNo for xml path('')) as test from @t as Y group by [name] /* (2 行受影响) test --------------- 李四,75 张三,90,59,39 */