34,588
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[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++
*/
----------------------------------------------------------------
-- 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 行受影响)
*/
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
姓名