select id,max(case when name='申请人' then string3 end) [申请人], max(case when name='地址' then string5 end) [地址] from tb group by id
这个不对的,你直接写死了,实际操作中我又不可能知道name的值~~
declare @sql varchar(8000) set @sql = 'select id ' select @sql = @sql + ' , max(case name when ''' + name + ''' then string3 else 0 end) [' + string3 + '], max(case name when ''' + name + ''' then string5 else 0 end) [' + string5 + ']' from (select distinct name from tb) as a set @sql = @sql + ' from tb group by id' exec(@sql)
declare @sql varchar(8000) set @sql = 'select id ' select @sql = @sql + ' , max(case name when ''' + name + ''' then '+col+' else 0 end) [' + col + '], max(case name when ''' + name + ''' then '+col+' else 0 end) [' + col + ']' from (select distinct name,col from tb) as a set @sql = @sql + ' from tb group by id ' exec(@sql)
你的数据可能导致结果出现很多列 name col 申请人 string3 申请人 string4
declare @sql varchar(8000) set @sql = 'select id ' select @sql = @sql + ' , max(case name when ''' + name + ''' then col else 0 end) [' + col + '], max(case name when ''' + name + ''' then col else 0 end) [' + col + ']' from (select distinct name from tb) as a set @sql = @sql + ' from tb group by id ' exec(@sql)
if object_id('[TB]') is not null drop table [TB] go create table [TB] (name nvarchar(6),col nvarchar(14),id int,string3 nvarchar(6),string5 nvarchar(4)) insert into [TB] select '申请人','string3',5,'neo','上海' union all select '地址','string5',5,'neo','上海' select * from [TB] declare @sql varchar(8000) DECLARE @sql_group VARCHAR(8000) select @sql = isnull(@sql + ',' , '') + name from (select (A.name + CASE WHEN TB.NAME is NULL THEN '' ELSE ' as '+TB.NAME END)AS name from sys.columns A left join TB on A.name = TB.col where object_id = object_id('TB') AND A.name NOT IN ('name','col'))t
select @sql_group =isnull(@sql_group + ',' , '') + name from (select A.name from sys.columns A left join TB on A.name = TB.col where object_id = object_id('TB') AND A.name NOT IN ('name','col'))t
SET @sql = 'select '+@sql +' from tb group by '+@sql_group EXEC(@sql)/* id 申请人 地址 5 neo 上海*/
ID 申请人 地址
5 neo 上海
max(case when name='地址' then string5 end) [地址]
from tb group by id
set @sql = 'select id '
select @sql = @sql + ' ,
max(case name when ''' + name + ''' then string3 else 0 end) [' + string3 + '],
max(case name when ''' + name + ''' then string5 else 0 end) [' + string5 + ']'
from (select distinct name from tb) as a
set @sql = @sql + ' from tb group by id'
exec(@sql)
declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' ,
max(case name when ''' + name + ''' then '+col+' else 0 end) [' + col + '],
max(case name when ''' + name + ''' then '+col+' else 0 end) [' + col + ']'
from (select distinct name,col from tb) as a
set @sql = @sql + ' from tb group by id '
exec(@sql)
name col
申请人 string3
申请人 string4
set @sql = 'select id '
select @sql = @sql + ' ,
max(case name when ''' + name + ''' then col else 0 end) [' + col + '],
max(case name when ''' + name + ''' then col else 0 end) [' + col + ']'
from (select distinct name from tb) as a
set @sql = @sql + ' from tb group by id '
exec(@sql)
go
create table [TB] (name nvarchar(6),col nvarchar(14),id int,string3 nvarchar(6),string5 nvarchar(4))
insert into [TB]
select '申请人','string3',5,'neo','上海' union all
select '地址','string5',5,'neo','上海'
select * from [TB]
declare @sql varchar(8000)
DECLARE @sql_group VARCHAR(8000)
select @sql = isnull(@sql + ',' , '') + name from (select (A.name + CASE WHEN TB.NAME is NULL THEN '' ELSE ' as '+TB.NAME END)AS name
from sys.columns A
left join TB on A.name = TB.col
where object_id = object_id('TB') AND A.name NOT IN ('name','col'))t
select @sql_group =isnull(@sql_group + ',' , '') + name from (select A.name
from sys.columns A
left join TB on A.name = TB.col
where object_id = object_id('TB') AND A.name NOT IN ('name','col'))t
SET @sql = 'select '+@sql +' from tb group by '+@sql_group
EXEC(@sql)/*
id 申请人 地址
5 neo 上海*/