Oracle - SQL to concatenate multiple rows [duplicate]

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?

Answer 1

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 |