if exists(select 1 from sysobjects where name=@tbname)
exec('drop table ['+@tbname+']')
select @s1='',@s2='',@s3=''
select @s1=@s1+',@'+cast(id as varchar)+' varchar(8000)'
,@s2=@s2+',@'+cast(id as varchar)+'=''
select userid=cast(userid as varchar)
,name=max(case xmmc when ''''姓名'''' then xmnr else '''''''' end)
,age=max(case xmmc when ''''年龄'''' then xmnr else '''''''' end)
,tel=max(case xmmc when ''''电话'''' then xmnr else '''''''' end)
from ['+name+'] group by userid'''
,@s3=@s3+'+'' union all ''+@'+cast(id as varchar)
from sysobjects
where name like 'tab_info_%' and xtype='U'
select @s1=substring(@s1,2,8000),@s2=substring(@s2,2,8000)
,@s3=substring(@s3,14,8000)
exec('declare '+@s1+'
select '+@s2+'
exec(''select * into ['+@tbname+'] from('+@s3+'+'')a'')
')
go
if exists(select 1 from sysobjects where name=@tbname)
exec('drop table ['+@tbname+']')
select @s1='',@s2='',@s3=''
select @s1=@s1+',@'+cast(id as varchar)+' varchar(8000)'
,@s2=@s2+',@'+cast(id as varchar)+'=''
select userid
,name=max(case xmmc when ''''姓名'''' then xmnr else '''''''' end)
,age=max(case xmmc when ''''年龄'''' then xmnr else '''''''' end)
,tel=max(case xmmc when ''''电话'''' then xmnr else '''''''' end)
from ['+name+'] group by userid'''
,@s3=@s3+'+'' union all ''+@'+cast(id as varchar)
from sysobjects
where name like 'tab_info_%' and xtype='U'
select @s1=substring(@s1,2,8000),@s2=substring(@s2,2,8000)
,@s3=substring(@s3,14,8000)
exec('declare '+@s1+'
select '+@s2+'
exec(''select * into ['+@tbname+'] from('+@s3+'+'')a'')
')
go
--测试数据
create table tab_info_1(userid char(2),xmmc varchar(10),xmnr varchar(10))
insert tab_info_1 select '01','姓名','a'
union all select '01','年龄','20'
union all select '01','电话','010-1234'
create table tab_info_2(userid char(2),xmmc varchar(10),xmnr varchar(10))
insert tab_info_2 select '02','姓名','b'
union all select '02','年龄','21'
union all select '02','电话','1234'
create table tab_info_3(userid char(2),xmmc varchar(10),xmnr varchar(10))
insert tab_info_3 select '03','姓名','c'
union all select '03','年龄','22'
union all select '03','电话','a1234'
go
select @s1=@s1+',@'+cast(id as varchar)+' varchar(8000)'
,@s2=@s2+',@'+cast(id as varchar)+'=''
select userid
,name=max(case xmmc when ''''姓名'''' then xmnr else '''''''' end)
,age=max(case xmmc when ''''年龄'''' then xmnr else '''''''' end)
,age=max(case xmmc when ''''电话'''' then xmnr else '''''''' end)
from ['+name+'] group by userid'''
,@s3=@s3+'+'' union all ''+@'+cast(id as varchar)
from sysobjects
where name like 'tab_info_%' and xtype='U'
select @s1=substring(@s1,2,8000),@s2=substring(@s2,2,8000)
,@s3=substring(@s3,14,8000)
exec('declare '+@s1+'
select '+@s2+'
exec('''+@s3+')
')
go
--调用
exec p_qry
go
--删除测试
drop table tab_info_1,tab_info_2,tab_info_3
drop proc p_qry
/*--测试结果
userid name age age
------ ---------- ---------- ----------
01 a 20 010-1234
02 b 21 1234
03 c 22 a1234
select @s1=@s1+',@'+cast(id as varchar)+' varchar(8000)'
,@s2=@s2+',@'+cast(id as varchar)+'=''
select userid
,name=max(case xmmc when ''''姓名'''' then xmnr else '''''''' end)
,age=max(case xmmc when ''''年龄'''' then xmnr else '''''''' end)
,age=max(case xmmc when ''''电话'''' then xmnr else '''''''' end)
from ['+name+'] group by userid'''
,@s3=@s3+'+'' union all ''+@'+cast(id as varchar)
from sysobjects
where name like 'tab_info_%' and xtype='U'
select @s1=substring(@s1,2,8000),@s2=substring(@s2,2,8000)
,@s3=substring(@s3,14,8000)
exec('declare '+@s1+'
select '+@s2+'
exec('''+@s3+')
')
go
一起考虑(偷懒了,用了全局临时表):
declare @sql varchar(2000)
set @sql='select * into ##a '
select @sql=@sql+' from '+name+' union all select * ' from sysobjects where xtype='u' and name like 'tab[_]info[_]___'
if len(@sql)>19
begin
set @sql=left(@sql,len(@sql)-19)
exec (@sql)
end
declare @sql1 varchar (2000)
set @sql1 =''
select @sql1=@sql1+',max(case xmmc when '''+xmmc+''' then xmnr else null end) as ['+xmmc+']'
from (select distinct xmmc from ##a) as x
set @sql1='select userid'+@sql1+' from ##a group by userid drop table ##a'
exec (@sql1)
declare @sql varchar(2000)
set @sql=''
select @sql=@sql+' select * from '+name+' union all' from sysobjects where xtype='u' and name like 'tab[_]info[_]___'
if len(@sql)>10
begin
set @sql=left(@sql,len(@sql)-10)
exec (@sql)
end
declare @sql varchar(2000)
set @sql=''
select @sql=@sql+' select * from '+name+' union all' from sysobjects where xtype='u' and name like 'tab[_]info[_]___'
set @sql=left(@sql,len(@sql)-10
exec (@sql)