if not object_id('Tempdb..#T') is null drop table #T Go Create table #T([danwei] nvarchar(2),[zhiwu] nvarchar(2),[xingming] nvarchar(2)) Insert #T select N'1队',N'队长',N'张三' union all select N'1队',N'队副',N'李四' union all select N'1队',N'政教',N'王五' union all select N'2队',N'队长',N'刘刘' union all select N'2队',N'队副',N'周强' Go declare @s nvarchar(2000) set @s='select [danwei]' Select @s=@s+','+quotename([zhiwu])+N'=max(case when [zhiwu]=N'''+[zhiwu]+''' then [xingming] else '''' end)' from #T group by [zhiwu] exec(@s+' from #T group by [danwei]') danwei 政教 队副 队长 ------ ---- ---- ---- 1队 王五 李四 张三 2队 周强 刘刘(2 個資料列受到影響)
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([danwei] nvarchar(2),[zhiwu] nvarchar(2),[xingming] nvarchar(2))
Insert #T
select N'1队',N'队长',N'张三' union all
select N'1队',N'队副',N'李四' union all
select N'1队',N'政教',N'王五' union all
select N'2队',N'队长',N'刘刘' union all
select N'2队',N'队副',N'周强'
Go
declare @s nvarchar(2000)
set @s='select [danwei]'
Select @s=@s+','+quotename([zhiwu])+N'=max(case when [zhiwu]=N'''+[zhiwu]+''' then [xingming] else '''' end)' from #T group by [zhiwu]
exec(@s+' from #T group by [danwei]')
danwei 政教 队副 队长
------ ---- ---- ----
1队 王五 李四 张三
2队 周强 刘刘(2 個資料列受到影響)