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

Question

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 1

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.