Re: COLLECT - collection of collections

"Björn Wächter" <bwc_at_p3-solutionsKILL_SPAM.de> a écrit dans le message de news: 7hu743F2ud5ptU1_at_mid.dfncis.de...
| Hi all,
|
| I'm looking for an aggregation function that can join collections to
| a new collection. I found nothing like this. An example:
|
| DDL:
|
| CREATE TABLE TEST_TAB
| (
| GROUP1 NUMBER,
| GROUP2 NUMBER,
| VAL NUMBER
| );
|
| DML:
|
| Insert into TEST_TAB (GROUP1, GROUP2, VAL)
| Values (1, 5, 2);
| Insert into TEST_TAB (GROUP1, GROUP2, VAL)
| Values (1, 5, 3);
| Insert into TEST_TAB (GROUP1, GROUP2, VAL)
| Values (1, 6, 1);
| Insert into TEST_TAB (GROUP1, GROUP2, VAL)
| Values (1, 6, 1);
| Insert into TEST_TAB (GROUP1, GROUP2, VAL)
| Values (1, 6, 6);
| Insert into TEST_TAB (GROUP1, GROUP2, VAL)
| Values (2, 3, 2);
| Insert into TEST_TAB (GROUP1, GROUP2, VAL)
| Values (2, 4, 1);
| Insert into TEST_TAB (GROUP1, GROUP2, VAL)
| Values (2, 4, 1);
| Insert into TEST_TAB (GROUP1, GROUP2, VAL)
| Values (2, 4, 8);
| Insert into TEST_TAB (GROUP1, GROUP2, VAL)
| Values (2, 4, 9);
| COMMIT;
|
|
| This is my query:
|
| SELECT
| GROUP1,
| COLLECT(COLLECTED_VAL)
| FROM
| (
| SELECT
| GROUP1,
| GROUP2,
| COLLECT(VAL) COLLECTED_VAL
| FROM
| TEST_TAB
| GROUP BY GROUP1, GROUP2
| )
| GROUP BY GROUP1;
|
|
| GROUP1 COLLECT(COLLECTED_VAL)(ELEMENT)
| ---------- -------------------------------
| 1 SYSTPdDrseXTFu3rgQAB/AQBfnQ==(S
| YSTPdDrseXTAu3rgQAB/AQBfnQ==((D
| ATASET)),SYSTPdDrseXTAu3rgQAB/A
| QBfnQ==((DATASET)))
|
|
| 2 SYSTPdDrseXTFu3rgQAB/AQBfnQ==(S
| YSTPdDrseXTAu3rgQAB/AQBfnQ==((D
| ATASET)),SYSTPdDrseXTAu3rgQAB/A
| QBfnQ==((DATASET)))
|
|
|
| 2 rows selected.
|
| But this returns a collection of collections.
| I want to union the collections to one single
| collection. Is this possible?
|
| Thanks,
| Björn

You should use stragg or wm_concat or the like.

SQL> select group1, wm_concat(collected_val) val   2 from (select group1, group2, wm_concat(val) collected_val

  3        from test_tab
  4        group by group1, group2)

---------- ----------------------------------------------------
         1 2,3,1,6,1
         2 2,1,8,9,1

2 rows selected.

Regards
Michel Received on Wed Sep 23 2009 - 12:52:59 CDT