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
This question is related to
sql
oracle
concatenation
string-aggregation
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;
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
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.
This OTN-thread contains several ways to do string aggregation, including a performance comparison: http://forums.oracle.com/forums/message.jspa?messageID=1819487#1819487
Source: Stackoverflow.com