Saturday, March 1, 2008

Oracle 10g: String Aggregation Technique 1


say for example I want to make a string aggregation for this table...what should

1. Create a function:

CREATE OR REPLACE FUNCTION concatenate_list (p_cursor IN SYS_REFCURSOR)
RETURN VARCHAR2
IS
l_return VARCHAR2(32767);
l_temp VARCHAR2(32767);
BEGIN
LOOP
FETCH p_cursor
INTO l_temp;
EXIT WHEN p_cursor%NOTFOUND;
l_return := l_return ',' l_temp;
END LOOP;
RETURN LTRIM(l_return, ',');
END;
/

2. Call the created function in any needed statement:

COLUMN sppcommon,spplocal FORMAT A50

SELECT dtlspp.speciesid,
concatenate_list(CURSOR(SELECT dtlspp.description
FROM gbl_species_dtl dtlspp
where dtlspp.speciesid=17)) spplocal
FROM gbl_species_dtl dtlspp
where dtlspp.speciesid=17
GROUP BY dtlspp.speciesid

3: Output:
SUB SPESIS 1,SUB SPESIS 3,American Samoa

No comments: