日期:2014-05-18 浏览次数:20552 次
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_number()over(partition by 姓名 order by getdate()),* from tb)t group by 姓名
------解决方案--------------------
---------------------------- -- 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 all select '张三','Java' union all select '张三','C++' union all select '李四','PHP' union all select '李四','C' union all select '王五','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 a set @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 行受影响) */
------解决方案--------------------
--> 测试数据:[test] if object_id('[test]') is not null drop table [test] create table [test]([姓名] varchar(4),[技能] varchar(4)) insert [test] select '张三','C#' union all select '张三','Java' union all select '张三','C++' union all select '李四','PHP' union all select '李四','C' union all select '王五','C#' select ROW_NUMBER()over(partition by 姓名 order by getdate()) as px, * INTO #test from test declare @str varchar(2000) set @str='' select @str=@str+',[技能'+LTRIM(px)+']=max(case when px='+LTRIM(px) +' then [技能] else '''' end)' from #test group by px exec('select [姓名]'+@str+' from #test group by [姓名]') /* 姓名 技能1 技能2 技能3 李四 PHP C 王五 C# 张三 C# Java C++ */