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
