--测试数据
create table t1(fieldname sysname,cnname sysname)
insert into t1
select 'spcd','产地'
union all select 'spcode','商品编号'
union all select 'spid','商品内码'
union all select 'spname','商品名称'
union all select 'sporder','总店商品编号'
union all select 'spspgg','规格'
union all select 'spsycx','适用车型'
union all select 'spxw','西文'
create table t2(spcd varchar(10),spid int,spname int)
insert into t2
select 'fdsdf',4234,235325
union all select 'efwre',24234,234234
union all select '23423',23423,23443
go
--查询处理
declare @s varchar(8000)
set @s=''
select @s=@s+',['+cnname+']=['+name+']'
from syscolumns a join t1 b on a.name=b.fieldname
where object_id('t2')=a.id
set @s=substring(@s,2,8000)
exec('select '+@s+' from t2')
go
declare @s varchar(8000)
set @s=''
select @s=@s+',['+cnname+']=['+name+']'
from syscolumns a join t1 b on a.name=b.fieldname
where object_id('t2')=a.id
set @s=substring(@s,2,8000)
exec('select '+@s+' from t2')
go
select IsNull(s.B+'('+t2.Aa+')',t2.Aa),IsNull(t.B+'('+t2.Bb+')',t2.Bb) from t2
left join t1 s on t2.Aa = s.A
left join t1 t on t2.Bb = t.A
/*结果
---------------------- ----------------------
序号(id) 编号(code)
1 2
*/
declare @a varchar(8000)
set @a='select spcd as '+(select cnname from t1 where fieldname='spcd')
+',spid '+(select cnname from t1 where fieldname='spid')
+',spname '+(select cnname from t1 where fieldname='spname')
+' from t2'
--print @a
exec(@a)