Re: is concat a group by function?

From: Vadim Tropashko <nospam_at_newsranger.com>
Date: Sat, 05 May 2001 02:59:49 GMT
Message-ID: <F0KI6.1941$vg1.159607_at_www.newsranger.com>

You don't have to wait until user-defined aggregates would be supported by 9i. Here is 8i solution:

CREATE TYPE number_list_t AS TABLE OF NUMBER;

CREATE
Function CONCAT_LIST
( lst IN number_list_t, separator varchar2) RETURN VARCHAR2 IS

ret                 varchar2(1000); 

BEGIN
FOR j IN 1..lst.LAST LOOP
ret := ret || separator || lst(j);
END LOOP; RETURN ret;
END; SELECT MGR, CONCAT_LIST(EMPLS, ', ') EMPLOYEES from ( SELECT
MGR,
CAST(MULTISET(SELECT empno FROM EMP e WHERE e.mgr=m.MGR ) AS number_list_t) EMPLS
FROM EMP m
where m.mgr is not null
group by MGR
);

MGR EMPLOYEES

------  -------------------- 

7566 7788, 7902
7698 7499, 7521, 7654, 7844, 7900
7782 7934
7788 7876
7839 7566, 7698, 7782
7902 7369

6 rows selected.

The question triggers some thoughts about connection between aggregates and collections...

BTW, Aloha, how is your AMS Ariphmetics Database?

In article <1IHI6.1820$vg1.151572_at_www.newsranger.com>, Mikito Harakiri says...
>
>In article <%zFI6.1690$vg1.141759_at_www.newsranger.com>, Aloha Kakuikanu says...
>>
>>SELECT
>>MGR,
>>CONCAT( EMPNO ) || ','
>>FROM SCOTT.EMP
>>group by MGR
>>
>>doesn't work, of course. Any other way to do it?
>>
>
>They'll have user defined aggregates in 9i.
>
Received on Sat May 05 2001 - 04:59:49 CEST