0

I'm trying to return a single description if the primary key are equal, for example, I have this return:

CD_TABLE | DS_TABLE | DS_ANOTHER_TABLE
------   | ------   | ------
1        | TEST-01  | TEST-X 
2        | TEST-02  | TEST-Y
2        | TEST-02  | TEST-W
3        | TEST-03  | TEST-Z

As second and third rows are the same information at the first column, is there a way to return this way?

CD_TABLE | DS_TABLE | DS_ANOTHER_TABLE
------   | ------   | ------
1        | TEST-01  | TEST-X 
2        | TEST-02  | TEST-Y, TEST-W
3        | TEST-03  | TEST-Z
Mureinik
  • 297,002
  • 52
  • 306
  • 350
dsd
  • 33
  • 5

1 Answers1

4

The listagg function seems to fit the bill:

SELECT   cd_table, ds_table,
         LISTAGG(ds_another_table, ', ') WITHIN GROUP (ORDER BY ds_another_table)
FROM     mytable
GROUP BY cd_table, ds_table
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • Bear in mind that `LISTAGG` has some limitations. If the result of concatenation results in more than 4000 characters, it will return an error. – Chris Hep Aug 30 '16 at 19:07
  • 1
    @HepC . . . That is less a limitation of `listagg()` than a limitation of the string data types in Oracle in general. You have the same problem is you try to concatenate strings using `||`. – Gordon Linoff Aug 30 '16 at 19:13
  • Thanks @Mureinik, but it worked with "WITHIN GROUP". Why my question received negative vote? – dsd Aug 30 '16 at 19:25
  • @dsd The "with" was indeed a typo, it should have been "within", fixed, thanks! As for the downvote - it wasn't me that downvoted it. – Mureinik Aug 30 '16 at 19:27
  • @Mureinik no problem, thanks again. – dsd Aug 30 '16 at 19:36