http://www.oracle.com/technology/oramag/code/tips2004/050304.html
1. create a function to concatenate strings.
CREATE OR REPLACE FUNCTION rowtocol(
p_slct IN VARCHAR2,
p_dlmtr IN VARCHAR2 DEFAULT ','
) RETURN VARCHAR2
AUTHID CURRENT_USER AS
TYPE c_refcur IS REF CURSOR;
lc_str VARCHAR2(4000);
lc_colval VARCHAR2(4000);
c_dummy c_refcur;
BEGIN
OPEN c_dummy FOR p_slct;
LOOP
FETCH c_dummy INTO lc_colval;
EXIT WHEN c_dummy%NOTFOUND;
lc_str := lc_str || p_dlmtr || lc_colval;
END LOOP;
CLOSE c_dummy;
RETURN SUBSTR(lc_str,2);
/*
EXCEPTION
WHEN OTHERS THEN
lc_str := SQLERRM;
IF c_dummy%ISOPEN THEN
CLOSE c_dummy;
END IF;
RETURN lc_str;
*/
END;
Usage :
select distinct t.author,
rowtocol('select book from table_name where author=''' || t.author|| '''')
from table_name t
;
or
select t.author, rowtocol('select book from table_name where author=''' || t.author|| '''', '#')
from table_name t
group by t.author
;
cons: less natural than user-defined aggregate function
pros: more flexible, aggregate function could take only one parameter