Oracle view grouping elements [duplicate]
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
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).
精彩评论