COLLECT - collection of collections

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 Received on Wed Sep 23 2009 - 04:12:35 CDT