Writing a Oracle Left Join to produce a denormalized delimited column

Question

Given:

Table_X

id        
-------
 1       
 2 
 3       
 4        

Table_Y

id_foreign   | content
-------------+-------------------
1            | A
1            | B
1            | C
1            | D
4            | E  
6            | F  
6            | G  

A regular left join Table_Y ON Table_X.id = Table_Y.id_foreign would produce normalized output, however this is not what I want in the end. Is it possible to easily produce the following without post processing outside of SQL? Content field is denormalized and separated by a delimiter:

id           | content
-------------+-------------------
1            | A,B,C,D
2            | 
3            | 
4            | E

Answer 1

You are looking for listagg():

select x.id, listagg(y.content, ',') within group (order by y.content)
from table_x x left join
     table_y y 
     on x.id = y.id_foreign
group by x.id;