get result seperated with commas in one row

Question

this is the query i wrote:

select d.canonical_name,
       l.locale_id,
       d.domain_id,
       ld.display_name,
       p.canonical_name as Identity_Set
  from locale_domain ld
  join domain d on ld.domain_id = d.domain_id
  join locale l on l.locale_id = ld.locale_id
  join domain_set ds on ds.domain_id = d.domain_id
  join set_property sp on sp.domain_set_id = ds.domain_set_id
  join property p on p.property_id = sp.property_id
 where d.canonical_name = 'Video Games'
   and ds.canonical_name = 'IDENTITY_SET'
   group by l.locale_id,d.canonical_name,
       d.domain_id,
       ld.display_name,p.canonical_name
 order by l.locale_id

this is the output:

Video Games 0   1794    Games                   Video Game Name
Video Games 0   1794    Games                   Video Game Platform
Video Games 1   1794    Video Games_de_DE   Video Game Name
Video Games 1   1794    Video Games_de_DE   Video Game Platform
Video Games 2   1794    Video Games_en_GB   Video Game Name
Video Games 2   1794    Video Games_en_GB   Video Game Platform

this is what i need:

Video Games 0   1794    Games                  Video Game Name, Video Game Platform
Video Games 1   1794    Video Games_de_DE      Video Game Name, Video Game Platform
Video Games 2   1794    Video Games_en_GB      Video Game Name, Video Game Platform

as you can see i need it distinct, and i need the result of the identity set seperated with commas

thanks

Which DBMS are you using? – Daniel Hilgarth May 07 '13 at 11:33 — Daniel Hilgarth, May 07 '13 at 11:33
hi i'm usin oracle pl sql – Arsenal IL Staff May 07 '13 at 11:41 — Arsenal IL Staff, May 07 '13 at 11:41
i'm using orcale 11.2 – Arsenal IL Staff May 07 '13 at 11:51 — Arsenal IL Staff, May 07 '13 at 11:51

score 0 · Accepted Answer · answered May 07 '13 at 13:14

0

You can do this with the listagg() function in Oracle 11:

with t as (<your query here>)
select cannonical_name, domain_id, ld.display_name,
       listagg(identity_set, ',') within group (order by locale_id)
from t
group by cannonical_name, domain_id, ld.display_name

answered May 07 '13 at 13:14

Gordon Linoff

1,242,037
58
646
786

thanks a lot work perfectly – Arsenal IL Staff May 08 '13 at 07:49

get result seperated with commas in one row

1 Answers1