Oracle sql - Case-CodePudding

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

CodePudding user response：

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>

CodePudding user response：

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