数据库表的行数据到列数据的转换

[ZJYUNCSUNDN]

现有表数据结构及数据如下:
表a
EmployeeID Name
K1263 AA
K1108 BB
K1123 CC
K1106 DD
K1143 EE
K1221 FF

表b
EmployeeID Suppy1 Suppy2 Suppy3 Suppy4 Suppy5
K1263 K1108 K1123 K1106 K1143 K1221

希望通过一个SQL查询形成这样的形势:
Suppy Name
K1108 BB
K1123 CC
K1106 DD
K1143 EE
K1221 FF

[-狙击手-]

declare @a table(EmployeeID varchar(10),Name varchar(10))
insert into @a select 'K1263','AA'
insert into @a select 'K1108','BB'
insert into @a select 'K1123','CC'
insert into @a select 'K1106','DD'
insert into @a select 'K1143','EE'
insert into @a select 'K1221','FF'

declare @b table(EmployeeID varchar(10),Suppy1 varchar(10),Suppy2 varchar(10),Suppy3 varchar(10),Suppy4 varchar(10),Suppy5 varchar(10))
insert into @b select 'K1263','K1108','K1123','K1106','K1143','K1221'


select EmployeeID ,Suppy=Suppy1 from @b union
select EmployeeID ,Suppy2 from @b union
select EmployeeID ,Suppy3 from @b union
select EmployeeID ,Suppy4 from @b union
select EmployeeID ,Suppy5 from @b

select m.Suppy,a.name
from @a a,(select EmployeeID ,Suppy=Suppy1 from @b union
select EmployeeID ,Suppy2 from @b union
select EmployeeID ,Suppy3 from @b union
select EmployeeID ,Suppy4 from @b union
select EmployeeID ,Suppy5 from @b) m
where m.Suppy = a.EmployeeID



/*

Suppy name
---------- ----------
K1108 BB
K1123 CC
K1106 DD
K1143 EE
K1221 FF

（所影响的行数为 5 行）

*/

[-狙击手-]

declare @a table(EmployeeID varchar(10),Name varchar(10))
insert into @a select 'K1263','AA'
insert into @a select 'K1108','BB'
insert into @a select 'K1123','CC'
insert into @a select 'K1106','DD'
insert into @a select 'K1143','EE'
insert into @a select 'K1221','FF'

declare @b table(EmployeeID varchar(10),Suppy1 varchar(10),Suppy2 varchar(10),Suppy3 varchar(10),Suppy4 varchar(10),Suppy5 varchar(10))
insert into @b select 'K1263','K1108','K1123','K1106','K1143','K1221'

select m.Suppy,a.name
from @a a,(select EmployeeID ,Suppy=Suppy1 from @b union
select EmployeeID ,Suppy2 from @b union
select EmployeeID ,Suppy3 from @b union
select EmployeeID ,Suppy4 from @b union
select EmployeeID ,Suppy5 from @b) m
where m.Suppy = a.EmployeeID



/*

Suppy name
---------- ----------
K1108 BB
K1123 CC
K1106 DD
K1143 EE
K1221 FF

（所影响的行数为 5 行）

*/

[brothersun1106]

up!

[ZJYUNCSUNDN]

我自己写了个用Union连接作的SQL，跟阿来的和子陌红尘的做了SQL的成本的分析，我的SQL花了48.89%，阿来的SQL花了17.03%，子陌红尘的SQL花了34.08%，最好的是阿来的SQL，其它俩个回复，我的问题很清楚，是在一个SQL查询中完成，所以我不能建立临时表，所以不能给分.

[WangZWang]

select a.EmployeeID,a.Name
from a inner Join
b on a.EmployeeID in (b.EmployeeID,b.Suppy1,
b.Suppy2,b.Suppy3,b.Suppy4,b.Suppy5)

[子陌红尘]

select
b.Suppy,a.Name
from
(select Suppy=Suppy1 from b union
select Suppy2 from b union
select Suppy3 from b union
select Suppy4 from b union
select Suppy5 from b) m,a
where
b.Suppy=a.EmployeeID

[子陌红尘]

declare @a table(EmployeeID varchar(10),Name varchar(10))
insert into @a select 'K1263','AA'
insert into @a select 'K1108','BB'
insert into @a select 'K1123','CC'
insert into @a select 'K1106','DD'
insert into @a select 'K1143','EE'
insert into @a select 'K1221','FF'

declare @b table(EmployeeID varchar(10),Suppy1 varchar(10),Suppy2 varchar(10),Suppy3 varchar(10),Suppy4 varchar(10),Suppy5 varchar(10))
insert into @b select 'K1263','K1108','K1123','K1106','K1143','K1221'

select
(select name from @a where EmployeeID=b.EmployeeID) EmployeeID,
(select name from @a where EmployeeID=b.Suppy1) Suppy1,
(select name from @a where EmployeeID=b.Suppy2) Suppy2,
(select name from @a where EmployeeID=b.Suppy3) Suppy3,
(select name from @a where EmployeeID=b.Suppy4) Suppy4,
(select name from @a where EmployeeID=b.Suppy5) Suppy5
from
@b b

/*
AA BB CC DD EE FF
*/

[WangZWang]

select a.EmployeeID,a.Name
from a inner Join b on a.EmployeeID in (
b.Suppy1,b.Suppy2, b.Suppy3,b.Suppy4,b.Suppy5)

[$扫地僧$]

这个意思啊!???
declare @a table(EmployeeID varchar(10),Name varchar(10))
insert into @a select 'K1263','AA'
insert into @a select 'K1108','BB'
insert into @a select 'K1123','CC'
insert into @a select 'K1106','DD'
insert into @a select 'K1143','EE'
insert into @a select 'K1221','FF'

declare @b table(EmployeeID varchar(10),Suppy1 varchar(10),Suppy2 varchar(10),Suppy3 varchar(10),Suppy4 varchar(10),Suppy5 varchar(10))
insert into @b select 'K1263','K1108','K1123','K1106','K1143','K1221'

select * from @a where EmployeeID not in (select EmployeeID from @b)