Friday, June 23

Oracle group_concat() updated

Update (again)! 2006-08-04
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.

3 comments:

male said...

For men who want bigger, harder, longer-lasting erections, there's now VigRX Plus™, a fresh twist on the already popular VigRX™, but designed to further enhance men's sexual functioning with the addition of three exciting new ingredients: Damiana, Tribulus, and Bioperin. Doctor endorsed and rated #1 for results by clients of penis enlargement consumers. rated two penis pills is vimax. if you find about male enhancement this products is the best and proven to work, there products have money-back guarantee in effectiveness and result.

Janice said...

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.

Raj said...

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.