测试:
create table t1(A varchar(10),B varchar(10))
insert t1 values ('code','编号')
insert t1 values ('id','序号')
create table t2(Aa varchar(10),Bb varchar(10))
insert t2 values('id','code')
insert t2 values('1','2')
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
*/
create table t1(A varchar(10),B varchar(10))
insert t1 values ('code','编号')
insert t1 values ('id','序号')
create table t2(Aa varchar(10),Bb varchar(10))
insert t2 values('id','code')
insert t2 values('1','2')
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
*/
create table t1(A varchar(10),B varchar(10))
insert t1 values ('code','编号')
insert t1 values ('id','序号')
create table t2(Aa varchar(10),Bb varchar(10))
insert t2 values('id','code')
insert t2 values('1','2')
select IsNull(t.B+'('+t2.Bb+')',t2.Bb),IsNull(s.B+'('+t2.Aa+')',t2.Aa) from t2
left join t1 s on t2.Aa = s.A
left join t1 t on t2.Bb = t.A/*结果
---------------------- ----------------------
编号(code) 序号(id)
2 1(所影响的行数为 2 行)*/
我要是的这样的结果!
列名为 编号(code) 序号(id)
记录1: 2 1
记录2: 3 2这样的效果行不?
fieldname cnname
spcd 产地
spcode 商品编号
spid 商品内码
spname 商品名称
sporder 总店商品编号
spspgg 规格
spsycx 适用车型
spxw 西文 t2为
spcd spid spname
fdsdf 4234 235325
efwre 24234 234234
23423 23423 23443
用一个语句查询结果为:产地 商品编号 商品内码 商品名称 这是列名
sdsdf 23234 23423 243534 这是查询的值
234 3453 3534 34534
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)
---------- ----------- -----------
fdsdf 4234 235325
efwre 24234 234234
23423 23423 23443
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
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--删除测试结果
drop table t1,t2/*--测试结果
产地 商品内码 商品名称
---------- ----------- -----------
fdsdf 4234 235325
efwre 24234 234234
23423 23423 23443
--*/