求教数据另类队列转换

求教数据另类行列转换
求静态的select 语句怎么做？能不能做？或者有什么好的方案修改输出的格式？

数据：
姓名 技能 
张三 C#
张三 Java
张三 C++
李四 PHP
李四 C
王五 C#


输出：
姓名 技能1 技能2 技能3.......
张三 C# Java C++
李四 PHP C
王五 C#

[解决办法]

SQL code

select    姓名,   max(case px when 1 then 技能 else '' end) as 技能1,   max(case px when 2 then 技能 else '' end) as 技能2,   max(case px when 3 then 技能 else '' end) as 技能3from   (select px=row_number()over(partition by 姓名 order by getdate()),* from tb)tgroup by    姓名
[解决办法]
探讨


SQL code
select 
   姓名,
   max(case px when 1 then 技能 else '' end) as 技能1,
   max(case px when 2 then 技能 else '' end) as 技能2,
   max(case px when 3 then 技能 else '' end) as 技能3
from
   (select px=row……

[解决办法]
SQL code
------------------------------ Author  :fredrickhu(小F，向高手学习)-- Date    :2012-07-06 13:20:41-- Version:--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) --    Apr 22 2011 11:57:00 --    Copyright (c) Microsoft Corporation--    Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]go create table [tb]([姓名] varchar(4),[技能] varchar(4))insert [tb]select '张三','C#' union allselect '张三','Java' union allselect '张三','C++' union allselect '李四','PHP' union allselect '李四','C' union allselect '王五','C#'--------------开始查询--------------------------declare @sql varchar(8000)set @sql = 'select 姓名 'select @sql = @sql + ' , max(case px when ''' + ltrim(px) + ''' then 技能 else '''' end) [技能' + ltrim(px) + ']'from (select distinct px from  (select px=row_number()over(partition by 姓名 order by getdate()),* from tb)t) as aset @sql = @sql + ' from  (select px=row_number()over(partition by 姓名 order by getdate()),* from tb)t group by 姓名'exec(@sql) ----------------结果----------------------------/* 姓名   技能1  技能2  技能3---- ---- ---- ----李四   PHP  C    王五   C#        张三   C#   Java C++(3 行受影响)*/
[解决办法]
SQL code
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([姓名] varchar(4),[技能] varchar(4))insert [test]select '张三','C#' union allselect '张三','Java' union allselect '张三','C++' union allselect '李四','PHP' union allselect '李四','C' union allselect '王五','C#'select     ROW_NUMBER()over(partition by 姓名 order by getdate()) as px,    * INTO #testfrom    testdeclare @str varchar(2000)set @str=''select @str=@str+',[技能'+LTRIM(px)+']=max(case when px='+LTRIM(px)       +' then [技能] else '''' end)'from #test group by pxexec('select [姓名]'+@str+' from #test group by [姓名]')/*姓名    技能1    技能2    技能3李四    PHP    C    王五    C#        张三    C#    Java    C++*/