Join multiple rows into a string

Question

I have 2 tables in Oracle: table1 and table2 (we have a very unimaginative DBA!)

table1 has column id (and a few others).

table2 has columns id, table1id and code. table1id is a foreign key to table1.

Usually there is 1 table2 row for each table1 row, but sometimes there are 2 table2 rows for a table1 row, and very occasionally 3.

What I need is a single 'code' value for each table1 row. If there is more than one coresponding table2 row, I need a concatenated string of all values returned, so the result might look like this:

table1.id code 1 a 2 b 3 a b 4 a b c

Any idea how this can be achieved? If it were SQL Server I'd write a function, although this would be slower than I would like. However I'm not as hot in Oracle as I am in SQL Server.

possible duplicate of SELECT returning multiple rows as string — APC
– APC, Commented Oct 17, 2012 at 11:10
Would be except that solution isn't available to me as our db version is only 10g. — CompanyDroneFromSector7G
– CompanyDroneFromSector7G, Commented Oct 17, 2012 at 11:22
That thread contains alternate solutions, including the WM_CONCAT alternative which is also mentioned in the answer you accepted. So, yes, it +is+ a duplicate. — APC
– APC, Commented Oct 17, 2012 at 12:56

Taryn · Accepted Answer · 2012-10-17 11:16:19Z

You did not specify what version of Oracle but. If you are using Oracle 11g, then you can use the LISTAGG() function:

select t1.id, listagg(t2.code, ' ') within group(order by t1.id) code from table1 t1 left join table2 t2 on t1.id = t2.id group by t1.id

If you are using Oracle 10g then you can use WM_CONCAT:

select t1.id, WM_CONCAT(t2.code) code from table1 t1 left join table2 t2 on t1.id = t2.id group by t1.id

why second sql gives me ORA-00979: "not a GROUP BY expression"? Exactly same as your second sql. Is this a bug of 10g..?

Aitor · Accepted Answer · 2012-10-17 11:55:08Z

@bluefeet has the correct solution, but if you are in 10g and don't want to use WM_CONCAT for some reason(for exemple, it's not documented), there is another one:

select t1.id, RTRIM(XMLAGG(XMLELEMENT(e,code,',').EXTRACT('//text()')),',') code from table1 t1 left join table2 t2 on t1.id = t2.id group by t1.id

Collectives™ on Stack Overflow

Join multiple rows into a string

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related