Another Way to Solve Last Problem.
http://www.oracle.com/technology/oramag/code/tips2004/050304.html1. 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 ASTYPE 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 tgroup by t.author;
cons: less natural than user-defined aggregate function
pros: more flexible, aggregate function could take only one parameter
页:
[1]