CODEWITHSUNDEEP
sqloracle-databaselistagg
L M
L M

Reputation: 136

LISTAGG within CASE statement

Is it possible to use listagg in the "Then" stage of a case statement?

I've been getting errors related to grouping and parenthesis even though i have grouped by every other selected field.

My case statement currently looks like:

MAX(CASE WHEN allergen.type = 'KTC' THEN listagg(allergen.name, ',') within group (order by allergen.name) END) AS Allergen_KTC

which works perfectly when used as below instead:

MAX(CASE WHEN allergen.type = 'KTC' THEN allergen.name END) AS Allergen_KTC

I think i'm missing something here.

Upvotes: 0

Views: 483

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Would vice-versa do any good?

listagg(case when allergen.type = 'KTC' then allergen.name end, ',') 
  within group (order by allergen.name)

Example:

SQL> select listagg(case when deptno = 10 then ename end, ',')
  2    within group (order by ename) result
  3  from emp;

RESULT
--------------------------------------------------------------
CLARK,KING,MILLER

SQL>

Upvotes: 1

Related Questions