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,300
declare @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.colid
select @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)
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.序号