有两张表，一张是产品表，一张是产品对应的供应商表。
一个产品对应的供应商不确定。
产品表字段
EDPNO
A
B
C
供应商表字段
EDPNO VENDOR
A JAPAN
A SUZHOU
A HANGZHOU
B HANGZHOU
B SUZHOU
C JAPAN
请教各位大虾可以实现这样的结果吗（如果供应商数量超过3个，继续在后面追加）
EDPNO VENDOR1 VENDOR2 VENDOR3
A JAPAN SUZHOU HANGZHOU
B HANGZHOU SUZHOU
C JAPAN
------解决方案--------------------
;with cte(EDPNO,VENDOR) as
(
select 'A','JAPAN'
union all select 'A','SUZHOU'
union all select 'A','HANGZHOU'
union all select 'B','HANGZHOU'
union all select 'B','SUZHOU'
union all select 'C','JAPAN'
)
select a.EDPNO,
stuff((select ', '+VENDOR from cte b
where b.EDPNO=a.EDPNO
for xml path('')),1,1,'') 'VENDOR'
from cte a
group by a.EDPNO
/*
EDPNO VENDOR
--------------------------------
A JAPAN, SUZHOU, HANGZHOU
B HANGZHOU, SUZHOU
C JAPAN
*/
这个结果能够接受吗
------解决方案--------------------
if object_id('cte') is not null
drop table cte
go
create table cte
(
EDPNO nvarchar(10),
VENDOR nvarchar(20)
)
go
insert into cte
select 'A','JAPAN'
union all select 'A','SUZHOU'
union all select 'A','HANGZHOU'
union all select 'B','HANGZHOU'
union all select 'B','SUZHOU'
union all select 'C','JAPAN'
go
select * from cte
go
--动态写法
DECLARE @s NVARCHAR(4000)
SET @s = ''
SELECT @s = @s + ',[VENDOR]'++'=max(case when [VENDOR]='
+ QUOTENAME([VENDOR], '''') + ' then [VENDOR] end)'
FROM cte
group by [VENDOR]
print @s
EXEC('select EDPNO'+@s+' from cte group by EDPNO')
go
--静态写法
select EDPNO
--,row_number()over(order by EDPNO)as id
,[VENDOR1]=max(case when [VENDOR]='HANGZHOU' then [VENDOR] end)
,[VENDOR2]=max(case when [VENDOR]='JAPAN' then [VENDOR] end)
,[VENDOR3]=max(case when [VENDOR]='SUZHOU' then [VENDOR] end)
from cte group by EDPNO
go
--值放在一列
select a.EDPNO,
stuff((select ', '+VENDOR from cte b
where b.EDPNO=a.EDPNO
for xml path('')),1,1,'') 'VENDOR'
from cte a
group by a.EDPNO