if object_id('[table1]') is not null drop table [table1]
create table [table1] (id int,zf varchar(100))
insert into [table1]
select 1,'雅思' union all
select 2,'新东方' union all
select 1,'托福' union all
select 2,'法师' union all
select 1,'大神'
SELECT id,nameStr into #temp
FROM(SELECT DISTINCT [id] FROM table1)A
OUTER APPLY(SELECT [nameStr]= STUFF(REPLACE(REPLACE(
(SELECT zf FROM table1 N WHERE [id] = A.id FOR XML AUTO
), '<N zf="', ','), '"/>', ''), 1, 1, ''))M
select * from #temp
1 雅思,托福,大神
create table [table1] (id int,zf varchar(100))
insert into [table1]
select 1,'雅思' union all
select 2,'新东方' union all
select 1,'托福' union all
select 2,'法师' union all
select 1,'大神'
SELECT id,nameStr into #temp
FROM(SELECT DISTINCT [id] FROM table1)A
OUTER APPLY(SELECT [nameStr]= STUFF(REPLACE(REPLACE(
(SELECT zf FROM table1 N WHERE [id] = A.id FOR XML AUTO
), '<N zf="', ','), '"/>', ''), 1, 1, ''))M
select * from #temp
1 雅思,托福,大神
2 新东方,法师