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

3

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

edited Oct 17, 2012 at 11:16

answered Oct 17, 2012 at 11:10

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Deckard Over a year ago

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

Taryn Over a year ago

@Deckard I'm not sure why you'd be getting that error.

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

1

@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

answered Oct 17, 2012 at 11:55

Aitor

3,4692 gold badges31 silver badges34 bronze badges

Collectives™ on Stack Overflow

Join multiple rows into a string

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related