Oracle: normalized fields to CSV string

oracle, plsql, sql

Solution

Mark,

If you are on version 11gR2, and who isn't :-), then you can use listagg

SQL> create table t (col1,col2)
  2  as
  3  select 'a', 'x' from dual union all
  4  select 'a', 'y' from dual union all
  5  select 'a', 'z' from dual union all
  6  select 'b', 'i' from dual union all
  7  select 'b', 'j' from dual union all
  8  select 'b', 'k' from dual
  9  /

Tabel is aangemaakt.

SQL> select col1
  2       , listagg(col2,',') within group (order by col2) col2s
  3    from t
  4   group by col1
  5  /

COL1  COL2S
----- ----------
a     x,y,z
b     i,j,k

2 rijen zijn geselecteerd.

If your version is not 11gR2, but higher than 10gR1, then I recommend using the model clause for this, as written here: http://rwijk.blogspot.com/2008/05/string-aggregation-with-model-clause.html

If lower than 10, then you can see several techniques in rexem's link to the oracle-base page, or in the link to the OTN-thread in the blogpost mentioned above.

Regards, Rob.

Problem

I have some one-many normalized data that looks like this. ``` a | x a | y a | z b | i b | j b | k ``` What query will return the data such that the "many" side is represented as a CSV string? ``` a | x,y,z b | i,j,k ```

Original source