CODEWITHSUNDEEP
sqloracle-databaseconcatenationstring-aggregation
Dan Polites
Dan Polites

Reputation: 6810

How can multiple rows be concatenated into one in Oracle without creating a stored procedure?

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

Upvotes: 80

Views: 376020

Answers (4)

Emmanuel
Emmanuel

Reputation: 14209

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;

Upvotes: 152

hegemon
hegemon

Reputation: 6764

(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

Upvotes: 45

Rob van Wijk
Rob van Wijk

Reputation: 17705

This OTN-thread contains several ways to do string aggregation, including a performance comparison: http://forums.oracle.com/forums/message.jspa?messageID=1819487#1819487

Upvotes: 0

jle
jle

Reputation: 9489

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;

Upvotes: 36

Related Questions