CODEWITHSUNDEEP
sqloracle-database
KATE SR
KATE SR

Reputation: 39

Oracle sql - Case

I have table like this:

ID PRODUCT
1 APPLE
2 SUMSUNG
2 APPLE
3 HUAWEI
4 XIAMO
4 APPLE

The product is devided into two types: ios and android. I want to get a table like this:

ID IOS ANDDROID
1 YES NO
2 YES YES
3 NO YES
4 YES YES

I tried :

select id,  
       case when product = 'APPLE' THEN 'YES' ELSE 'NO' END as 'IOS',
       case when product in ('SUMSUNG', 'HUAWEI', 'XIAMO') THEN 'YES' ELSE 'NO' END AS 'ANDROID'
from table

But i got this table:

ID IOS ANDDROID
1 YES NO
2 YES NO
2 NO YES
3 NO YES
4 YES NO
4 NO YES

Upvotes: 0

Views: 65

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65158

You can construct the logic depending on the brand APPLE,as that's the unique member(APPLE) for its own operating system (IOS) such as

SELECT id,
       DECODE(SIGN(SUM(DECODE(product,'APPLE',1,0))),1,'YES','NO') AS ios,
       DECODE(SIGN(SUM(DECODE(product,'APPLE',0,1))),1,'YES','NO') AS android 
  FROM t
 GROUP BY id
 ORDER BY id

where the inner conditionals along with SUM() aggregation by grouped by id column and the signum function check the existence/unexistence of APPLE by binary digits, and then replaces them with the words YES/NO

Demo

Upvotes: 1

SQL Hacks
SQL Hacks

Reputation: 1332

You can aggregate to ensure that the id only shows up once:

select id,  
       MAX(case when product = 'APPLE' THEN 'YES' END) as 'IOS',
       MAX(case when product in ('SUMSUNG', 'HUAWEI', 'XIAMO') THEN 'YES' END) AS 'ANDROID'
from table
GROUP BY id

That ensures you get one row per id - but you get NULL instead of 'NO'. To fix that use COALESCE.

select id,  
       COALESCE(MAX(case when product = 'APPLE' THEN 'YES' END),'NO') as 'IOS',
       COALESCE(MAX(case when product in ('SUMSUNG', 'HUAWEI', 'XIAMO') THEN 'YES' END),'NO') AS 'ANDROID'
from table
GROUP BY id

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142720

You'll need yet another - mapping - table which shows which operating system belongs to which product. Yes, you can hardcode it into the query, but that's generally a bad idea. You can't modify query every time new product appears on the market, can you? Sure you can, but you shouldn't.

SQL> with mapping (product, os) as
  2    (select 'APPLE'  , 'IOS'     from dual union all
  3     select 'SAMSUNG', 'ANDROID' from dual union all
  4     select 'HUAWEI' , 'ANDROID' from dual union all
  5     select 'XIAMO'  , 'ANDROID' from dual
  6    ),
  7  your_table (id, product) as
  8    (select 1, 'APPLE'   from dual union all
  9     select 2, 'SAMSUNG' from dual union all
 10     select 2, 'APPLE'   from dual union all
 11     select 3, 'HUAWEI'  from dual union all
 12     select 4, 'XIAMO'   from dual union all
 13     select 4, 'APPLE'   from dual
 14    )
 15  select y.id,
 16    max(case when p.os = 'IOS' then 'YES' else 'NO' end) as ios,
 17    max(case when p.os = 'ANDROID' then 'YES' else 'NO' end) as android
 18  from your_table y join mapping p on p.product = y.product
 19  group by y.id
 20  order by y.id;

        ID IOS AND
---------- --- ---
         1 YES NO
         2 YES YES
         3 NO  YES
         4 YES YES

SQL>

Upvotes: 2

Related Questions