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.
EDIT Dec 2024: You can also remove duplicates by using the DISTINCT option from Oracle 19c:
SELECT question_id,
LISTAGG(DISTINCT element_id, ',') WITHIN GROUP (ORDER BY element_id)
FROM YOUR_TABLE
GROUP BY question_id;
v$version or product_component_version. 11.2 indicates 11gR2.
GROUP BY question_id ?
(WARNING - WM_CONCAT is an unsupported function that was removed in version 12c. Unless you're using a very old database, you should avoid this function.)
Easy:
SELECT question_id, wm_concat(element_id) as elements
FROM questions
GROUP BY question_id;
Pesonally tested on 10g ;-)
From http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
ORA-06502: PL/SQL: numeric or value error: character string buffer too small if the concatenated value happens to exceed the max length of 4000 bytes.
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 elements
FROM (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 questionid
CONNECT BY prev = PRIOR curr AND questionid = PRIOR questionid
START WITH curr = 1;
ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have separator as part of column value.... And if the concatenated value happens to exceed the max length of 4000 bytes, you'd get the error: ORA-01489: result of string concatenation is too long.This OTN-thread contains several ways to do string aggregation, including a performance comparison: http://forums.oracle.com/forums/message.jspa?messageID=1819487#1819487
