How to use listagg properly with case statements

I am trying to use listagg but I’m getting the wrong output. Normally, I would use each case statement separately, but then how would I use listagg?

Table A:

 JAN     FEB    MAR    APR    Tag
 C                            102
          D            T      100
          D                   101
 C        D      B     T      103                  

Table B:

 Name       Tag
 Ally       100
 Ben        101
 Missy      102
 Noah       103

Desired Output:

 Ally  Dog,Turtle
 Ben   Dog
 Missy Chicken
 Noah  Chicken,Dog,Bird,Turtle

My attempted (wrong) code:

 select listagg(
   nvl(
     case when a.jan = 'C' then 'Chicken'
          when a.feb = 'D' then 'Dog'
          when a.mar = 'B' then 'Bird'
          when a.apr = 'T' then 'Turtle'
      end,','),'none')
  within group (order by a.tag)
      from a where a.tag = b.tag 

Answer

Here’s one option, which uses multiple self-joins.

• lines #1 – 14 represent your sample data
• anima CTE is here to simplify code; better have it in a table (even if it is a CTE) than use CASE
• the final result, lines #24 – 34, concatenates animal names
  • trim + regexp_replace is used to remove superfluous commas

Here you go:

SQL> with
  2  -- sample data
  3  taba (jan, feb, mar, apr, tag) as
  4    (select 'c' , null, null, null, 102 from dual union all
  5     select null, 'd' , null, 't' , 100 from dual union all
  6     select null, 'd' , null, null, 101 from dual union all
  7     select 'c' , 'd' , 'b' , 't' , 103 from dual
  8    ),
  9  tabb (name, tag) as
 10    (select 'ally' , 100 from dual union all
 11     select 'ben'  , 101 from dual union all
 12     select 'missy', 102 from dual union all
 13     select 'noah' , 103 from dual
 14    ),
 15  --
 16  -- replace animal codes with their names
 17  anima (code, name) as
 18    (select 'c', 'chicken' from dual union all
 19     select 'd', 'dog'     from dual union all
 20     select 'b', 'bird'    from dual union all
 21     select 't', 'turtle'  from dual
 22    )
 23  -- the final result
 24  select b.name,
 25    trim(',' from regexp_replace(n1.name ||','|| n2.name ||','|| n3.name ||','|| n4.name,
 26                                 '( *, *){2,}', ','
 27                                )
 28        ) result
 29  from tabb b join taba a on a.tag = b.tag
 30  left join anima n1 on n1.code = a.jan
 31  left join anima n2 on n2.code = a.feb
 32  left join anima n3 on n3.code = a.mar
 33  left join anima n4 on n4.code = a.apr
 34  order by b.name;

NAME  RESULT
----- ------------------------------
ally  dog,turtle
ben   dog
missy chicken
noah  chicken,dog,bird,turtle

SQL>