Oracle - SQL to concatenate multiple rows

Question

Let’ say I have a table with 3 columns

   ID INTEGER,
   OFFSET INTEGER,
   STR VARCHAR(50)

Containing values:

ID          OFFSET   STR
1              1              TestSTR1
1              2              TestSTR3
1              3              TestSTR5
2              1              TestSTR4
2              2              TestSTR2
3              1              TestSTR6

I would like to pull the concatenated “STR” for each ID (ordered by OFFSET). So basically, what I want is:

ID           STR
1              TestSTR1TestSTR3TestSTR5
2              TestSTR4TestSTR2
3              TestSTR6

Any thoughts on how would you construct a similar query?

score 9 · Accepted Answer · answered Oct 07 '13 at 04:57

If you have Oracle 11g you could use the LISTAGG() function for this:

SELECT
      id
    , listagg(str) WITHIN GROUP (ORDER BY OFFSET) AS str_of_str
FROM yourtable
GROUP BY id

see: http://docs.oracle.com/cd/E11882_01/server.112/e10592/functions089.htm and this sqlfiddle

  | ID |               STR_OF_STR |
    |----|--------------------------|
    |  1 | TestSTR1TestSTR3TestSTR5 |
    |  2 |         TestSTR4TestSTR2 |
    |  3 |                 TestSTR6 |

score 8 · Answer 2 · edited Oct 07 '13 at 05:34

8

in Oracle 11g you can use this query:

SELECT 
ID, 
LISTAGG(STR, ' ') WITHIN GROUP (ORDER BY OFFSET) AS STR
FROM  Table
GROUP BY ID;

in **Oracle 9i** you can use this query:

SELECT 
ID,
rtrim (xmlagg (xmlelement(e,STR||' ')).extract ('//text()'), ' ') AS STR
FROM  Table
GROUP BY ID;

edited Oct 07 '13 at 05:34

Prahalad Gaggar

11,389
16
53
71

answered Oct 07 '13 at 05:28

Hamidreza

3,038
1
18
15

Oracle - SQL to concatenate multiple rows

2 Answers2