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

Pleas provide precise information about the error - error code, error message etc. — krokodilko
– krokodilko, Commented Oct 20, 2013 at 7:03
The errormessage is somewhat misleading - there is no apostrophes after ename and before Job from emp - try this query: SELECT STRING_AGGREGATE('select ename '||' Job is '|| 'Job from emp') ENAMES FROM DUAL; — krokodilko
– krokodilko, Commented Oct 20, 2013 at 7:21

eggyal · Accepted Answer · 2014-05-16 14:07:20Z

9

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

edited May 16, 2014 at 14:07

eggyal

127k18 gold badges220 silver badges244 bronze badges

answered May 16, 2014 at 13:49

Erol Irez

991 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hol · Accepted Answer · 2013-10-20 07:25:38Z

1

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.)

answered Oct 20, 2013 at 7:25

hol

8,4615 gold badges37 silver badges63 bronze badges

5 Comments

user1837746 Over a year ago

now this query give me same error

SELECT DEPTNO,STRING_AGGREGATE('SELECT ENAME FROM  EMP WHERE DEPTNO='||DEPTNO,',' and job='Programmer')ENAME FROM EMP GROUP BY DEPTNO ORDER BY 1

hol Over a year ago

Every time you have a ' between the first and last ' you must double the ' like this ''. Okay? This means "escaping". Also I think there is something wrong about the DEPTNO. I think it should read:

SELECT DEPTNO,STRING_AGGREGATE('SELECT ENAME FROM EMP WHERE DEPTNO='||DEPTNO||' and job=''Programmer''')ENAME FROM EMP GROUP BY DEPTNO ORDER BY 1

user1837746 Over a year ago

hol i try to check your query but it give me error missing right parenthesis

user1837746 Over a year ago

this query work for me

SELECT DEPTNO,STRING_AGGREGATE('SELECT ENAME FROM EMP WHERE DEPTNO='||DEPTNO,',')ENAME FROM EMP GROUP BY DEPTNO ORDER BY 1

but when i use and job=''Programmer'' its not working

hol Over a year ago

Maybe it is a uppercase / lowercase problem. Can you try lower(job) = ''programmer'' ?

Collectives™ on Stack Overflow

String Aggregation in Oracle: Multiple Rows into Single Column

2 Answers 2

Comments

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Linked

Related