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 
Add a comment |
4 Answers
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; 
6 Comments
Somu
As a side note, we can know the database version by querying the table
v$version or product_component_version. 11.2 indicates 11gR2.
Almas
at any rate it helped me
elrado
Using it for a long time and this is as simple as it gets.
Mike Honey
Is this missing
GROUP BY question_id ?
Jared
Is there a way to eliminate duplicates in element_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
8 Comments
Rob van Wijk
Note though that wm_concat is an undocumented function. So you might want to think twice before using this in production code.
Dan Polites
Very cool. Works for me too, but I'll need to make sure this function is available in our production environment.
Tom Stickel
I get Error --> ORA-00904 WM_CONCAT : Invalid Identifier
Somu
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.
hello_earth
i used to use wmsys.wm_concat(...) for most of my quick and dirty jobs in oracle 11g (Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production)
|
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; 
2 Comments
Dan Polites
Works great! ROW_NUMBER() is what I was missing to make sys_connect_by_path work for me.
Somu
Note that when this technique is applied to concatenate a field whose value may contain the separator, the following error is thrown:
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
