create table 表T (f1 int, f2 varchar(10))insert into 表T select 1, 'aaa' union all select 2, 'bbb' union all select 2, 'ccc' select a.f1, replace((select '|'+f2 from 表T b where b.f1=a.f1 for xml path('')),'|','') 'f2' from 表T a group by a.f1
with S as ( select f1,f2 from 表T group by f1,f2 ) select t.f1,stuff((select ''+t2.f2 from S t2 where t2.f1 = t.f1 for xml path('')),1,O,'')'f2' from S t group by t.f1
加个distinct即可,create table 表T (f1 int, f2 varchar(10))insert into 表T select 1, 'aaa' union all select 2, 'bbb' union all select 2, 'ccc' union all select 2, 'ccc' select f1,f2 from 表T /* f1 f2 ----------- ---------- 1 aaa 2 bbb 2 ccc 2 ccc(4 row(s) affected) */ select a.f1, replace((select distinct '|'+f2 from 表T b where b.f1=a.f1 for xml path('')),'|','') 'f2' from 表T a group by a.f1
create table 表T
(f1 int, f2 varchar(10))insert into 表T
select 1, 'aaa' union all
select 2, 'bbb' union all
select 2, 'ccc'
select a.f1,
replace((select '|'+f2
from 表T b
where b.f1=a.f1
for xml path('')),'|','') 'f2'
from 表T a
group by a.f1
/*
f1 f2
----------- -------------
1 aaa
2 bbbccc(2 row(s) affected)
*/
from 表T t group by f1
1 aaa
2 bbb
2 ccc
2 ccc
最后结果仍然是
1 aaa
2 bbbccc
就是说值在合并的时候需要去重再拼接起来。请问满足这步需要怎么做?
with S as
(
select f1,f2 from 表T group by f1,f2
)
select t.f1,stuff((select ''+t2.f2 from S t2 where t2.f1 = t.f1 for xml path('')),1,O,'')'f2'
from S t
group by t.f1
(f1 int, f2 varchar(10))insert into 表T
select 1, 'aaa' union all
select 2, 'bbb' union all
select 2, 'ccc' union all
select 2, 'ccc'
select f1,f2 from 表T
/*
f1 f2
----------- ----------
1 aaa
2 bbb
2 ccc
2 ccc(4 row(s) affected)
*/
select a.f1,
replace((select distinct '|'+f2
from 表T b
where b.f1=a.f1
for xml path('')),'|','') 'f2'
from 表T a
group by a.f1
/*
f1 f2
----------- -------------
1 aaa
2 bbbccc(2 row(s) affected)
*/