concatenating one to many values to one line separated by commas in sql join

I have a join which shares a one to many relationship to one table. I would to in stead of returning this value:

125|PROGRAM1|OEM2|1
125|PROGRAM1|OEM2|2
125|PROGRAM1|OEM2|3

I want to return one line like this:

125|PROGRAM1|OEM2|1,2,3

Here is the sql i am running now:

select d.layout_id,d.pgm_nm,d.corp_nm  from io_layout_output d
    join  (select f.program_nm, c.corp_nm from file_config f
    join corp_config c
    on f.output_id = c.output_id
      where f.output_id = 112) b
    on d.pgm_nm = b.program_nm and d.corp_nm = b.corp_nm 

How would I end up with the correct output?

Answer

You need to use a function for that. See LISTAGG

select d.layout_id,
       d.pgm_nm,
       LISTAGG(d.corp_nm, ', ') as corp_nm
  from io_layout_output d
    join  (select f.program_nm, 
                  c.corp_nm 
             from file_config f
                    join corp_config c
                          on f.output_id = c.output_id
            where f.output_id = 112) b
       on d.pgm_nm = b.program_nm 
       and d.corp_nm = b.corp_nm
group by d.layout_id,
         d.pgm_nm

EDIT:

Last time I used Oracle you could use LISTAGG using group by. I just looked at the docs and it doesn’t mention it anymore. Here is the way if above does not work:

select d.layout_id,
       d.pgm_nm,
       LISTAGG(d.corp_nm, ', ') 
           WITHIN GROUP (ORDER BY d.layout_id, d.pgm_nm) as corp_nm 
  from io_layout_output d
    join  (select f.program_nm, 
                  c.corp_nm 
             from file_config f
                    join corp_config c
                          on f.output_id = c.output_id
            where f.output_id = 112) b
       on d.pgm_nm = b.program_nm 
       and d.corp_nm = b.corp_nm

Note: I’m just showing how to use the function. Did not look at your query at all. Adding this note because your result data does not match the number of columns on your SQL