declare @s varchar(8000) set @s='' select @s=@s+',['+字段名称+']=max(case 字段编号 when ' +cast(字段编号 as varchar)+' then 字段值 else '' end' from a表 exec('select 记录编号'+@s+' from b表 group by 记录编号')
--上面的错了一点,改一下:declare @s varchar(8000) set @s='' select @s=@s+',['+字段名称+']=max(case 字段编号 when ' +cast(字段编号 as varchar)+' then 字段值 else '''' end)' from a表 exec('select 记录编号'+@s+' from b表 group by 记录编号')
肯定要过程了: declare @exec varchar(8000) select @exec='' selct @exec=@exec+'(case when 字段编号='+convert(varchar(20),字段编号)+'then max(字段值) end ) as '+字段名称 +',' from a set @exec=left(@exec,len(@exec)-1) set @exec ='select 记录编号 '+@exec +' from b group by 记录编号'
--下面是数据测试--创建数据测试环境 create table a表(字段编号 int,字段名称 varchar(10)) insert into a表 select 1,'Field1' union all select 2,'Field2' union all select 3,'Field3'create table b表(字段编号 int,字段值 varchar(10),记录编号 int) insert into b表 select 1,'V11',1 union all select 2,'V12',1 union all select 3,'V13',1 union all select 1,'V21',2 union all select 2,'V22',2 union all select 3,'V23',2 union all select 1,'V31',3 union all select 2,'V32',3 union all select 3,'V33',3 --数据处理 declare @s varchar(8000) set @s='' select @s=@s+',['+字段名称+']=max(case 字段编号 when ' +cast(字段编号 as varchar)+' then 字段值 else '''' end)' from a表 exec('select 记录编号'+@s+' from b表 group by 记录编号')go --删除数据测试环境 drop table a表,b表
set @s=''
select @s=@s+',['+字段名称+']=max(case 字段编号 when '
+cast(字段编号 as varchar)+' then 字段值 else '' end'
from a表
exec('select 记录编号'+@s+' from b表 group by 记录编号')
set @s=''
select @s=@s+',['+字段名称+']=max(case 字段编号 when '
+cast(字段编号 as varchar)+' then 字段值 else '''' end)'
from a表
exec('select 记录编号'+@s+' from b表 group by 记录编号')
declare @exec varchar(8000)
select @exec=''
selct @exec=@exec+'(case when 字段编号='+convert(varchar(20),字段编号)+'then max(字段值) end ) as '+字段名称 +',' from a
set @exec=left(@exec,len(@exec)-1)
set @exec ='select 记录编号 '+@exec +' from b group by 记录编号'
create table a表(字段编号 int,字段名称 varchar(10))
insert into a表
select 1,'Field1'
union all select 2,'Field2'
union all select 3,'Field3'create table b表(字段编号 int,字段值 varchar(10),记录编号 int)
insert into b表
select 1,'V11',1
union all select 2,'V12',1
union all select 3,'V13',1
union all select 1,'V21',2
union all select 2,'V22',2
union all select 3,'V23',2
union all select 1,'V31',3
union all select 2,'V32',3
union all select 3,'V33',3
--数据处理
declare @s varchar(8000)
set @s=''
select @s=@s+',['+字段名称+']=max(case 字段编号 when '
+cast(字段编号 as varchar)+' then 字段值 else '''' end)'
from a表
exec('select 记录编号'+@s+' from b表 group by 记录编号')go
--删除数据测试环境
drop table a表,b表
记录编号 Field1 Field2 Field3
----------- ---------- ---------- ----------
1 V11 V12 V13
2 V21 V22 V23
3 V31 V32 V33