CODEWITHSUNDEEP
sqloracle-database
Skn
Skn

Reputation: 101

Case when to return multiple values

here is my table, I would use a decode to display the following values in an everyday scenario, however, I need to display all values in 1 column for a specific report as they correspond to the same item.

T1:

Id V1 V2 V3 V4 V5 V5_Text   
1  1  1  1  0  0   Other    
2  0  1  1  0  1   Other     
3  0  0  1  0  1   QWE    
4  0  0  1  1  0   ABC    
5  1  0  0  0  1   Other

I used case when but I cannot seem to return more than 1 value.

select id,
case when V1=1, then 'A'
     when V2=1, then 'B'
     when V3=1, then 'C'
     when V4=1, then 'D'
     when V5=1, then v5_text

Expected output: 

1  A,B,C,Other   
2  B,C,Other    
3  C,QWE    
4  C,D,ABC   
5  A,Other

Actual Output: 

1  Other    
2  Other    
3  QWE     
4  ABC    
5  Other

Upvotes: 0

Views: 2298

Answers (3)

F.Kahalaj
F.Kahalaj

Reputation: 86

i hope this help you, i test this code with your raw data

select id,
rtrim((case v1 when 1 then 'A,' else '' end ||
case v2   when 1 then 'B,' else '' end ||
case v3   when 1 then 'C,' else '' end ||
case  v4  when 1 then 'D,' else '' end ||
case  v5 when 1 then v5_text || ',' else '' end),',')
from table1

image of result

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35603

Not sure what else you have in mind, nor how large this table is, but another way is to "unpivot" that v1...v5 structure and then use listagg()

btw: the first row

Id V1 V2 V3 V4 V5 V5_Text   
1  1  1  1  0  0   Other   
   A  B  C                   -- there is no "other" here

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE Table1
    (ID int, V1 int, V2 int, V3 int, V4 int, V5 int, V5_TEXT varchar2(5))
;

INSERT ALL 
    INTO Table1 (ID, V1, V2, V3, V4, V5, V5_TEXT)
         VALUES (1, 1, 1, 1, 0, 0, 'Other')
    INTO Table1 (ID, V1, V2, V3, V4, V5, V5_TEXT)
         VALUES (2, 0, 1, 1, 0, 1, 'Other')
    INTO Table1 (ID, V1, V2, V3, V4, V5, V5_TEXT)
         VALUES (3, 0, 0, 1, 0, 1, 'QWE')
    INTO Table1 (ID, V1, V2, V3, V4, V5, V5_TEXT)
         VALUES (4, 0, 0, 1, 1, 0, 'ABC')
    INTO Table1 (ID, V1, V2, V3, V4, V5, V5_TEXT)
         VALUES (5, 1, 0, 0, 0, 1, 'Other')
SELECT * FROM dual
;

Query 1:

select
      id, listagg(alpha,',') within group (order by alpha) codes_concat
from (
    select id, v1, V5_TEXT, 'A' alpha from table1 where v1 = 1 union all
    select id, v2, V5_TEXT, 'B' alpha from table1 where v2 = 1 union all
    select id, v3, V5_TEXT, 'C' alpha from table1 where v3 = 1 union all
    select id, v4, V5_TEXT, 'D' alpha from table1 where v4 = 1 union all
    select id, v5, V5_TEXT, 'Other'   from table1 where v5 = 1 
    ) d
group by
      id

Results:

| ID | CODES_CONCAT |
|----|--------------|
|  1 |        A,B,C |
|  2 |    B,C,Other |
|  3 |      C,Other |
|  4 |          C,D |
|  5 |      A,Other |

edit

if union all offends, then there are other ways:

select
      id, listagg(alpha,',') within group (order by alpha) codes_concat
from (
      select 
            id
          , V5_TEXT
          , case when cj.n = 1 then v1
                 when cj.n = 2 then v2
                 when cj.n = 3 then v3
                 when cj.n = 4 then v4
                 when cj.n = 5 then v5 end v1
          , case when cj.n = 1 then 'A'
                 when cj.n = 2 then 'B'
                 when cj.n = 3 then 'C'
                 when cj.n = 4 then 'D'
                 when cj.n = 5 then V5_TEXT end alpha
      from table1
      cross join (
          Select level n from dual connect by level<=5
          ) cj
    ) d
where v1 <> 0
group by
      id

;

or unpivot may be used etc. etc.

select
      id, listagg(alpha,',') within group (order by alpha) codes_concat
from (
      select
            id, v5_text, v1
          , case when vname = 'V1' then 'A'
                 when vname = 'V2' then 'B'
                 when vname = 'V3' then 'C'
                 when vname = 'V4' then 'D'
                 when vname = 'V5' then v5_text end alpha
      from table1
      UNPIVOT ( 
              v1 FOR( vname ) IN ( v1, v2, v3, v4, v5
              )
          )
    ) d
where v1 <> 0
group by
      id
;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270081

You need to concatenate the values:

select id,
       trim(',' from
            (case when V1 = 1 then 'A,' end) ||
             case when V2 = 1 then 'B,' end) ||
             case when V3 = 1 then 'C,' end) ||
             case when V4 = 1 then 'D,' end) ||
             case when V5 = 1 then v5_text
            )
           )

Upvotes: 2

Related Questions