I know how to do this in T-SQL, you use the FOR XML PATH(‘’) construct like so:

with t  as(

 select 'Charles' parent, 'William' child union

 select 'Charles', 'Harry' union

 select 'Anne', 'Peter' union

 select 'Anne', 'Zara' union

 select 'Andrew', 'Beatrice' union

 select 'Andrew', 'Eugenie' 

)

SELECT parent, STUFF( ( SELECT ','+ child 

                        FROM t a 

                        WHERE b.parent = a.parent 

                        FOR XML PATH('')),1 ,1, '')  children

FROM t b 

GROUP BY parent

which, yes, turned this:

into this:

Unfortunately I didn’t know how to accomplish it in Oracle however after a bit of searching around I found the answer:

with t  as(

 select 'Charles' parent, 'William' child from dual union

 select 'Charles', 'Harry' from dual union

 select 'Anne', 'Peter' from dual union

 select 'Anne', 'Zara' from dual union

 select 'Andrew', 'Beatrice' from dual union

 select 'Andrew', 'Eugenie' from dual

)

select parent, rtrim(xmlagg(xmlelement(e,child || ','))

                .extract('//text()'),',') childs from t

group by parent

So, now you know. And so will I if I ever need to find this again!

------------------------------------------------------------------------------------------------

最新简单方法

select id, [value] = stuff((select ','+ [value] from tb t where id = tb.id for xml path('')) ,1,1,'')

from tb

group by id

select id, [value] = (select [value] from tb t where id = tb.id for xml auto) 

from tb

group by id

------------------------------------------------

CREATE TABLE tb(id int, [value] varchar(10))
INSERT tb SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'

select * from tb

SELECT *
FROM (SELECT DISTINCT id FROM tb) A OUTER APPLY(
        SELECT [values]= STUFF(REPLACE(REPLACE(
            (
                SELECT value FROM tb N
                WHERE id = A.id
                FOR XML AUTO
            ), '', ''), 1, 1, '')
)N

select stuff('abc',2,3,'xyz')
--stuff 函數刪除指定長度的字符,并在指定的起點位置插入另一組字符.
stuff(character_expression,start,length,insert_expression)

select * from
(select distinct [姓名]from dbo.Student ) a outer apply
(select [科目]=
(select [科目] from dbo.Student where a.[姓名]=[姓名]
for xml auto)
) b

select point ,[value] as name
 from t T1
outer apply
(select [value]=stuff(replace(replace((select name from t where  T1.point >= t.point for xml auto ),'',''),1,1,'')
)
T2

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE

  create function m_str (@id int)

returns varchar(8000)

  as

  begin

  declare @str varchar(8000)

  set @str=''

  select @str=@str +','+ value from ta where id=@id

set @str=STUFF(@str,1,1,'')

  return @str

  end