0

I have table prod and have three column id,name and maturity as shown below.

            id      name    maturity
            -------------------------------
            224     cash    1d
            224     cash    2d
            224     cash    3d
            224     cash    4d
            225     bond    5w
            225     bond    6w
            225     bond    7w
            225     bond    8w

Here i need the output like( i mean if i fire an sql statement)

            224     cash    1d,2d,3d,4d
            225     bond    5w,6w,7w,8w

can some oone please help in this.

user272735
  • 10,473
  • 9
  • 65
  • 96
Ashok Kumar Dabbadi
  • 235
  • 3
  • 6
  • 12

1 Answers1

0

The LISTAGG function is what you are looking for (in Oracle 11g at least).

A full syntax example:

SELECT id, name, LISTAGG(maturity, ', ') WITHIN GROUP (ORDER BY maturity) FROM your_table GROUP BY id

Note: You can also change the grouping condition to GROUP BY name, if needed.

Bud Damyanov
  • 30,171
  • 6
  • 44
  • 52
  • Thanq for your quick responce, but here i am using 10g version – Ashok Kumar Dabbadi May 09 '14 at 08:48
  • 1
    @AshokKumarDabbadi - then why did you tag the question with both 10g and 11g? [There are various methods for doing this in earlier versions](http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php), and plenty of questions here about it (many of which point to that article). – Alex Poole May 09 '14 at 09:16