How can multiple rows be concatenated into one in Oracle without creating a stored procedure?
concatenation, oracle, sql, string-aggregation
Solution
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;
Problem
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 ```