Oracle SQL 'select' with 1 column having combined values

Question

Table t1:

pk_id | col1 | col2 | col3
===========================
1     | val1 | val2 | val3

Table t2: (fk_id is foreign key references to pk_id)

fk_id | col4
=============
1     | val4A
1     | val4B
1     | val4C

My SQL query is:

select pk_id,col1,col2,col3,col4
from t1 left join t2 on t1.pk_id=t2.fk_id;

The result is:

pk_id | col1 | col2 | col3 | col4
===================================
1     | val1 | val2 | val3 | val4A
1     | val1 | val2 | val3 | val4B
1     | val1 | val2 | val3 | val4C

But I actually want this result:

pk_id | col1 | col2 | col3 | col4
===============================================
1     | val1 | val2 | val3 | val4A;val4B;val4C

How to change the 'select' query to achieve this result with col4 value is the combined values of val4A, val4B, val4C (separated by semicolons)?

At your position I would create a function that returns `varchar2` which wold be aggregation of the right values from 2nd table. — yamny, Aug 29 '14 at 07:11
http://stackoverflow.com/questions/4686543/sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle — a_horse_with_no_name, Aug 29 '14 at 07:15
How about LISTAGG? http://docs.oracle.com/cd/E11882_01/server.112/e10592/functions089.htm — Konstantin V. Salikhov, Aug 29 '14 at 07:18
@a_horse_with_no_name - well, I need to use `LISTAGG` more, I keep forgetting about that... — yamny, Aug 29 '14 at 07:19

Yigitalp Ertem · Accepted Answer · 2014-08-29T08:18:38.707

2

You can use LISTAGG for that.

select pk_id,col1,col2,col3,
                    LISTAGG (t2.col4, ';') WITHIN GROUP (ORDER BY t2.col4) AS col4
from t1 left join t2 on t1.pk_id=t2.fk_id
group by pk_id, col1, col2, col3;

edited Aug 29 '14 at 08:18

answered Aug 29 '14 at 07:17

Yigitalp Ertem

1,821
22
25

error: ORA-00937: not a single-group group function – jondinham Aug 29 '14 at 07:39
1

Ah, sorry for that, I added the group by clause. – Yigitalp Ertem Aug 29 '14 at 08:19

Oracle SQL 'select' with 1 column having combined values

1 Answers1