sql一张表的列值匹配另一张表的字段名，要如何实现？请高手帮忙

[spjloveu123]

SQL 数据库有两种表
表Ａ字段名为：A1,A2,A3
对应的记录值为：3，2，6
表B的字段名为：B1,B2
对应的记录值为：A1,东
A2,南
A3,西
就是说表B是对表A的字段名的一个解释，如何用一个查询语句得到如下记录集：
A1,东，3
A2,南，2
A3,西，6
就是每条记录既能显示A的值，也能出现A的对应解释，如何实现？

[linghu0619]

SELECT
select name from sysobject where id = object_id("A"),select * from B

[linghu0619]

SELECT
aa.name,bb.b1,bb.b2
FROM
(select name from sysobject where id = object_id("A")) as aa,
(select * from B) as bb
WHERE
aa.name = bb.b1

[linghu0619]

没有环境测试，哥们你自己试试吧，大概提供一个思路哈

[天-笑]

declare @A table (A1 int ,A2 int ,A3 int)
insert into @A 
select 3,2,6

declare @B table(B1 CHAR(2),B2 nchar(1))
insert into @B
select 'A1','东' union all
select 'A2','南' union all
select 'A3','西'

select *,case	when B1 = 'A1' then (select  A1 from @A )
				when B1 = 'A2' then (select  A2 from @A )	
				when B1 = 'A3' then (select  A3 from @A )	
		 else null end as A
 from @b

/*


(1 行受影响)

(3 行受影响)
B1   B2   A
---- ---- -----------
A1   东    3
A2   南    2
A3   西    6

(3 行受影响)

*/

[spjloveu123]

如果B表的值有几十个，这种写法貌似不是很好的解决办法

[以学习为目的]

select  B.b1,B.b2,
        (select case when B1 = 'A1' then (select  A1 from A )
                     when B1 = 'A2' then (select  A2 from A )    
                     when B1 = 'A3' then (select  A3 from A )    
         else null 
         end 
from B
group by B1)