CASE statement with LIKE during LISTAGG Operation

Question

+-----------+------------+-----------+-----------+
| Person_ID | First_Name | Last_Name | Igroup_ID |
+-----------+------------+-----------+-----------+
|         2 | Rick       | Hudson    |       100 |
|         2 | Rick       | Hudson    |        50 |
|         2 | Rick       | Hudson    |        28 |
|         2 | Rick       | Hudson    |        15 |
|         3 | John       | Hardy     |       150 |
|         3 | John       | Hardy     |       100 |
|         4 | Tom        | Johnson   |       200 |
|         4 | Tom        | Johnson   |       150 |
|         4 | Tom        | Johnson   |       100 |
+-----------+------------+-----------+-----------+

Please refer the above table the reason behind the usage of operation LISTAGG , I am getting Mutliple rows of Person ID since I have each person associated with multiple Group ID , So I wanted to Concatenate the Group ID using LISTAGG and seggregate the values.

And since there are so many values IGROUP Values a each person can associate , But we worried about first 2 values , In the screenshot

When something returns 100|50 Then 'GroupA' When something returns 150|100 Then 'GroupB' When something returns 200|150 Then 'GroupC'

(CASE LISTAGG(G.IGROUP_ID , '|') WITHIN GROUP (ORDER BY G.igroup_id) 
          WHEN  '100|50' THEN 'GroupA'
          WHEN  '150|100' THEN 'GroupB'
          WHEN  '200|150 THEN 'GroupC'
     END) AS SERVICES

But here the problem is if I use above way I have to define all the combinations in the CASE statement

Is there any way I can use WHEN LIKE '100|50%' THEN GroupA

Answer 1

Create a flag column for each value that interest you.
Once you have it you can easily do whatever analyze you need.

select    person_id

         ,max (case when igroup_id =  15 then 1 else 0 end) as is_15
         ,max (case when igroup_id =  28 then 1 else 0 end) as is_28
         ,max (case when igroup_id =  50 then 1 else 0 end) as is_50
         ,max (case when igroup_id = 100 then 1 else 0 end) as is_100
         ,max (case when igroup_id = 150 then 1 else 0 end) as is_150
         ,max (case when igroup_id = 200 then 1 else 0 end) as is_200

from      mytable

group by  person_id

order by  person_id      

---

+-----------+-------+-------+-------+--------+--------+--------+
| PERSON_ID | IS_15 | IS_28 | IS_50 | IS_100 | IS_150 | IS_200 |
+-----------+-------+-------+-------+--------+--------+--------+
|         2 |     1 |     1 |     1 |      1 |      0 |      0 |
|         3 |     0 |     0 |     0 |      1 |      1 |      0 |
|         4 |     0 |     0 |     0 |      1 |      1 |      1 |
+-----------+-------+-------+-------+--------+--------+--------+

---

Simply extract only the first 2 values and check them

(
    CASE    regexp_substr
            (
                LISTAGG(G.IGROUP_ID , '|') WITHIN GROUP (ORDER BY G.igroup_id)
               ,'[^|]*\|[^|]*'
            )

        WHEN '50|100'  THEN 'Yes'
        WHEN '100|100' THEN 'No'
    END
) AS SERVICES