Some even better code was posted here!
Found a quick nifty peace of code over at asktom.
The code gives a very cheap and simple way to do group concatiation, same as group_concat() in MySQL. This without using PL/SQL. The code uses the new collect function introduced in Oracle 10g, so unfortunatly it is 10g only. 9i people will have to stay with other methods such as the PL/SQL function stragg().
CREATE OR REPLACE TYPE ntt_varchar2 AS TABLE
OF VARCHAR2(4000);
CREATE OR REPLACE FUNCTION to_string (
nt_in IN ntt_varchar2,
delimiter_in IN VARCHAR2 DEFAULT ','
) RETURN VARCHAR2 IS
v_idx PLS_INTEGER;
v_str VARCHAR2(32767);
v_dlm VARCHAR2(10);
BEGIN
v_idx := nt_in.FIRST;
WHILE v_idx IS NOT NULL LOOP
v_str := v_str || v_dlm || nt_in(v_idx);
v_dlm := delimiter_in;
v_idx := nt_in.NEXT(v_idx);
END LOOP;
RETURN v_str;
END to_string;
/
And a quick example:
SQL> select deptno, cast(collect(ename) as ntt_varchar2) as vals
from emp group by deptno
DEPTNO VALS
---------- -------------------------------------------------------------------
10 NTT_VARCHAR2('CLARK', 'KING', 'MILLER')
20 NTT_VARCHAR2('SMITH', 'JONES', 'SCOTT', 'ADAMS', 'FORD')
30 NTT_VARCHAR2('ALLEN', 'WARD', 'MARTIN', 'BLAKE', 'TURNER', 'JAMES')
NTT_VARCHAR2('JAMES')
SQL> select deptno, to_string(cast(collect(ename) as ntt_varchar2)) as vals
from emp group by deptno;
DEPTNO VALS
---------- -------------------------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
JAMES
Cool!
The origianl thread can be found here.
Thanks to Adrian Billington for coming up with the code.
2 comments:
You did a valuable post. By the way, your choice of topic is interesting that gives me reason to finish my reading.You can also visit my posted topic articles which is about Male Enhancement Products and Health tips as well.
select deptno,
cast(collect(ename) as ntt_varchar2)
as vals ,
rtrim(
xmlserialize(content
extract(
xmlagg(xmlelement("e", ename||',') order by deptno)
, '//text()'
)
)
, ','
) as vals2
from emp group by deptno
This is an alternative method to get the values as CSV.
Post a Comment