Oracle view grouping elements

Question

Possible Duplicate:
Oracle: Combine multiple results in a subquery into a single comma-separated value

Hi there,

this is my problem...

I have a table:

+------+------+------+ 
| CODE | NAME | TYPE |
+------+------+------+
|  1   | AAA  |  x   |
+------+------+------+
|  2   | BBB  |  x   |
+------+------+------+
|  3   | CCC  |  y   |
+------+------+------+
|  4   | DDD  |  y   |
+------+------+------+

I wanna make a view in ORACLE .... I wanna that the result is:

+---------+------+
| NAME    | TYPE |
+---------+------+
| AAA;BBB |   x  |
+---------+------+
| CCC;DDD |   y  |
+---------+------+

Can I grouping AAA and BBB because they have same TYPE in a VIEW that in a NAME will be "AAA;BBB" ... so grouping various names divided with ;

Can anyone help me?

Regards,

Tommaso

Also: http://stackoverflow.com/questions/1120706/is-there-an-oracle-sql-query-that-aggregates-multiple-rows-into-one-row — OMG Ponies, Mar 03 '11 at 17:21

score 2 · Answer 1 · answered Mar 03 '11 at 14:57

Tim Hall has a page that covers the various string aggregation techniques available in Oracle depending on the Oracle version, what packages are installed in the database, and whether you can create new procedures to support this or whether you want it done in pure SQL.

If you are using 11.2, the simplest option would be to use the built-in LISTAGG analytic funciton

SELECT listagg(name, ';') within group (order by code), type
  FROM your_table
 GROUP BY type

If you are using an earlier version, my preference would be to use the custom aggregate function (Tim's string_agg).

Oracle view grouping elements

1 Answers1