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

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

SELECT question_id,       LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id)FROM YOUR_TABLEGROUP 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.

Easy:

SELECT question_id, wm_concat(element_id) as elementsFROM   questionsGROUP BY question_id;

Pesonally tested on 10g ;-)

From http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php

There are many way to do the string aggregation, but the easiest is a user defined function. Try this for a way that does not require a function. As a note, there is no simple way without the function.

This is the shortest route without a custom function: (it uses the ROW_NUMBER() and SYS_CONNECT_BY_PATH functions )

SELECT questionid,       LTRIM(MAX(SYS_CONNECT_BY_PATH(elementid,','))       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS elementsFROM   (SELECT questionid,               elementid,               ROW_NUMBER() OVER (PARTITION BY questionid ORDER BY elementid) AS curr,               ROW_NUMBER() OVER (PARTITION BY questionid ORDER BY elementid) -1 AS prev        FROM   emp)GROUP BY questionidCONNECT BY prev = PRIOR curr AND questionid = PRIOR questionidSTART WITH curr = 1;