日期:2014-05-17 浏览次数:20581 次
;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&nbs