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
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.