select 表2.*,表1.字段名称 as 字段名称 into #temp from 表2,表1 where 表2.系號=表1.系號select 序號,a=case 字段名称 when 'a ' then a
when 'b' then b
when 'c' then c
end
from #temp
drop table #temp
when 'b' then b
when 'c' then c
end
from #temp
drop table #temp
--建立测试环境
Create table 表1
(序号 Int,
字段名称 Varchar(10)
)Create table 表2
(序号 Int,
A Int,
B Int,
C INt
)
GO
--插入数据
Insert 表1 Values(1, 'A')
Insert 表1 Values(2, 'B')
Insert 表1 Values(3, 'C')Insert 表2 Values(1, 1, 10, 100)
Insert 表2 Values(2, 2, 20, 200)
Insert 表2 Values(3, 3, 30, 300)
GO
--测试
select A.序号,
(Case 字段名称
When 'A' Then A
When 'B' Then B
When 'C' Then C
End ) As 值
from 表1 A Inner Join 表2 B On A.序号=B.序号--删除测试环境
Drop table 表1,表2
--结果
/*
序号 值
1 1
2 20
3 300
*/
create table tb2(tid int,A INT,B INT,C INT)insert into tb1
select 1,'A'
UNION ALL SELECT 2,'B'
UNION ALL SELECT 3,'C'insert into tb2
select 1,1,10,100
UNION ALL SELECT 2,2,20,200
UNION ALL SELECT 3,3,30,300declare @str nvarchar(4000)
select @str=''
select @str=@str+' union all select tid,'''+a.name + ''' as tname,'+a.name + ' as tvalue from tb2' from syscolumns a,sysobjects b where a.id=b.id and b.xtype='U' and b.name='tb2' and a.name<>'tid' order by a.colidselect @str=stuff(@str,1,11,'')
select @str = 'select a.tid,b.tvalue from tb1 a,('+ @str + ') b where a.tid=b.tid and a.tname=b.tname'
exec(@str)drop table tb1,tb2
id tvalue
1 1
2 20
3 300