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

Question

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

Emmanuel · Answer 1 · 2019-04-16T10:53:52.217

144

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.

edited Apr 16 '19 at 10:53

answered Sep 14 '12 at 09:14

Emmanuel

13,935
12
50
72

4

As a side note, we can know the database version by querying the table `v$version` or `product_component_version`. 11.2 indicates 11gR2. – Somu Jun 04 '13 at 07:26
at any rate it helped me – Almas Feb 23 '15 at 11:57
Using it for a long time and this is as simple as it gets. – elrado May 26 '15 at 07:48
5

Is this missing `GROUP BY question_id` ? – Mike Honey Oct 11 '16 at 05:50

score 45 · Answer 2 · edited Dec 14 '21 at 04:11

45

(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

edited Dec 14 '21 at 04:11

Jon Heller

34,999
6
74
132

answered Jul 02 '09 at 19:28

hegemon

6,614
2
32
30

4

Note though that wm_concat is an undocumented function. So you might want to think twice before using this in production code. – Rob van Wijk Jul 02 '09 at 19:34
Very cool. Works for me too, but I'll need to make sure this function is available in our production environment. – Dan Polites Jul 06 '09 at 12:02
3

I get Error --> ORA-00904 WM_CONCAT : Invalid Identifier – Tom Stickel Sep 27 '12 at 20:10
1

`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. – Somu Jun 04 '13 at 07:22
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) – hello_earth Jul 03 '13 at 07:46
You saved my time and teach me a good lesson. Thank you – Arash moradabadi Jun 07 '17 at 08:21
This is simple solution than listagg function – anandchaugule Feb 04 '21 at 10:34
Worked perfectly on my production 10g 10.2.0.4.0. Great answer. Thanks – Mazhar Ehsan May 08 '23 at 08:48

jle · Accepted Answer · 2009-07-02T18:59:24.503

35

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;

edited Jul 02 '09 at 18:59

answered Jul 02 '09 at 18:54

jle

9,316
5
48
67

Works great! ROW_NUMBER() is what I was missing to make sys_connect_by_path work for me. – Dan Polites Jul 06 '09 at 12:22
2

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.` – Somu Jun 04 '13 at 07:20

score 0 · Answer 4 · edited Oct 03 '17 at 21:33

0

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

edited Oct 03 '17 at 21:33

C B

1,677
6
18
20

answered Jul 02 '09 at 19:37

Rob van Wijk

17,555
5
39
55

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

4 Answers4

Linked

Related