给你一个例子:
create table t (id int identity,name varchar(10),code varchar(10))
insert t values('人口','rk')
insert t values('经济','jj')
insert t values('文化','wh')
insert t values('土地','td')
declare @sql varchar(1000)
set @sql = ''
select @sql = @sql+name+'=max(case when name='''+name+''' then code else null end),' from t
--print @sql
set @sql = left(@sql,len(@sql) - 1)
set @sql = 'select '+@sql+' from t'
exec (@sql)
drop table t
create table t (id int identity,name varchar(10),code varchar(10))
insert t values('人口','rk')
insert t values('经济','jj')
insert t values('文化','wh')
insert t values('土地','td')
declare @sql varchar(1000)
set @sql = ''
select @sql = @sql+name+'=max(case when name='''+name+''' then code else null end),' from t
--print @sql
set @sql = left(@sql,len(@sql) - 1)
set @sql = 'select '+@sql+' from t'
exec (@sql)
drop table t
insert t values('人口',20)
insert t values('经济',12)
insert t values('文化',15)
insert t values('土地',45)
declare @sql varchar(1000)
set @sql = ''
select @sql = @sql+name+'=max(case when name='''+name+''' then code else null end),' from t
--print @sql
set @sql = left(@sql,len(@sql) - 1)
set @sql = 'select [姓名]=''年龄'', '+@sql+' from t'
exec (@sql)
drop table t
/*
姓名 人口 经济 文化 土地
---- ----------- ----------- ----------- -----------
年龄 20 12 15 45
*/
insert into @t select 20,'a'
insert into @t select 18,'b'
insert into @t select 9 ,'c'declare @s1 varchar(8000),@s2 varchar(8000),@i int
set @s1='select ''姓名'' as col1'
set @s2='select ''年龄'' as col1'
set @i=2
select @s1=@s1+','''+姓名+''' as col'+rtrim(@i),@s2=@s2+','''+rtrim(年龄)+'''',@i=@i+1 from @t
exec('select * from('+@s1+' union '+@s2+') a order by case a.col1 when ''姓名'' then 1 else 2 end')/*
col1 col2 col3 col4
---- ---- ---- ----
姓名 a b c
年龄 20 18 9
*/