How can multiple rows be concatenated into one in Oracle without creating a stored procedure?

Dan Polites picture Dan Polites · Jul 2, 2009 · Viewed 263.7k times · Source

How can I achieve the following in oracle without creating a stored procedure?

Data Set:

question_id    element_id
1              7
1              8
2              9
3              10
3              11
3              12

Desired Result:

question_id    element_id
1              7,8
2              9
3              10,11,12

Answer

Emmanuel picture Emmanuel · Sep 14, 2012

From Oracle 11gR2, the LISTAGG clause should do the trick:

SELECT question_id,
       LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id)
FROM YOUR_TABLE
GROUP BY question_id;

Beware if the resulting string is too big (more than 4000 chars for a VARCHAR2, for instance): from version 12cR2, we can use ON OVERFLOW TRUNCATE/ERROR to deal with this issue.