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

"Björn Wächter" <bwc (AT) p3-solutionsKILL_SPAM (DOT) de> a écrit dans le message de news: 7hu743F2ud5ptU1 (AT) mid (DOT) dfncis.de...
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)
5 group by group1
6 /
GROUP1 VAL
---------- ----------------------------------------------------
1 2,3,1,6,1
2 2,1,8,9,1

2 rows selected.

Regards
Michel