34,593
社区成员
发帖
与我相关
我的任务
分享
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
/*
f1 f2
----------- -------------
1 aaa
2 bbbccc
(2 row(s) affected)
*/
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
select f1,stuff((select ''+f2 from 表T where f1 = t.f1 for xml path('') ),1,0,'')
from 表T t group by 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)
*/