Another Way to Solve Last Problem.

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