String Aggregation in Oracle: Multiple Rows into Single Column

Question

hi I have following function for string aggregation in oracle

CREATE OR REPLACE FUNCTION STRING_AGGREGATE(i_query VARCHAR2,
i_seperator VARCHAR2 DEFAULT ',')
RETURN VARCHAR2
AS
  l_return CLOB:='';
  l_temp VARCHAR(32000);
  TYPE r_cursor is REF CURSOR;
  rc r_cursor;
BEGIN
  OPEN rc FOR i_query;
  LOOP
    FETCH rc
    INTO L_TEMP;
    EXIT WHEN RC%NOTFOUND;
    l_return:=l_return||L_TEMP||i_seperator;
  END LOOP;
  RETURN RTRIM(l_return,i_seperator);
END;

when i call this function it show like this

SELECT STRING_AGGREGATE('select ename from emp') ENAMES FROM DUAL;

ENAMES
---------------------------
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER

this function working good here but when i call this function with multiple column with space it give me error here is my query

SELECT STRING_AGGREGATE('select ename||' Job is '||Job from emp') ENAMES FROM DUAL;

i want to get result

smith job is clerk,allen job is accountatnt,ward job is programmer etc..

please guide me how to achieve this

Answer 1

I know this is an old question, but I thought I would offer the solution using Oracle's delivered function, LISTAGG.

Run:

select listagg(ename || ' is a ' || job, ', ')
within group (order by job, ename)
from   emp
where  job in ('MANAGER','ANALYST','CLERK');

You will get:

FORD is a ANALYST, SCOTT is a ANALYST, ADAMS is a CLERK, JAMES is a CLERK, MILLER is a CLERK, SMITH is a CLERK, BLAKE is a MANAGER, CLARK is a MANAGER, JONES is a MANAGER

Answer 2

You have to escape the single-quotes

SELECT STRING_AGGREGATE('select ename||'' Job is ''||Job from emp') ENAMES FROM DUAL;

You can try out out how you pass the string to the function like this

SELECT 'select ename||'' Job is ''||Job from emp' FROM DUAL;

which gives you

select ename||' Job is '||Job from emp

See the demo: http://sqlfiddle.com/#!2/d41d8/23283

(By the way. There is a new feature LISTAGG since Oracle 11g which you may also want to have a look at.)