sql - 如何编写 SQL 代码将 a 中的表聚合到 b

Question

表 a 的结构如下

EMP_ID  DATE_OF_PURCHASE    ITEM_PURCHASED  
------------------------------------------
1       01-JAN-19           BAG
2       01-JAN-19           UMBRELLA
1       03-JAN-19           UMBRELLA
3       01-JAN-19           PERFUME
1       04-JAN-19           PERFUME

我想把它聚合到表 b 中，看起来像

EMP_ID  BAG     UMBRELLA    PERFUME
------------------------------------
1       YES     YES         YES
2       NO      YES         NO  
3       NO      NO          YES

标签： sql

Answer 1

尝试这个：

select emp_id,
case when BAG = 1 then 'YES' else 'NO' end as BAG,
case when UMBRELLA = 1 then 'YES' else 'NO' end as UMBRELLA,
case when PERFUME = 1 then 'YES' else 'NO' end as PERFUME
FROM
(select emp_id,
       max(case when item_purchased = 'BAG' then 1 else 0 end) as BAG,
       max(case when item_purchased = 'UMBRELLA' then 1 else 0 end) as UMBRELLA,
       max(case when item_purchased = 'PERFUME' then 1 else 0 end) as PERFUME
from t
group by emp_id);

干杯！！