Unpivot String Column into 4 new Columns SQL / Snowflake

Question

I am struggling to come up with a solution to trying to convert the type column below, into 3 seperate columns which would count those event types for me. Working in Snowflake on this when creating a view.

I have this sample table which is brought up using this code to see all events that happened for 1 customer.

select id, what_id, who_id, event_date, type
from table1
where who_id like 'id_1'

| id | what_id | who_id | event_date | type |
|----|---------| -------|------------|------|
|1   |null     |id_1    |2023-02-24  |CALL  |
|2   |null     |id_1    |2023-02-27  |EMAIL |
|3   |null     |id_1    |2023-02-22  |EMAIL |
|4   |null     |id_1    |2023-02-25  |OTHER |

The desired output would be unpivoting the type column in exchange for 4 new columns as below:

| id | what_id | who_id | event_date | total_calls | total_emails | total_other | total_all |
|----|---------| -------|------------|-------------|--------------|-------------|-----------|
|1   |null     |id_1    |2023-02-24  |1            |0             |0            |1          |
|2   |null     |id_1    |2023-02-27  |0            |1             |0            |1          |
|3   |null     |id_1    |2023-02-22  |0            |1             |0            |1          |
|4   |null     |id_1    |2023-02-25  |0            |0             |1            |1          |

Any help would be greatly appreciated. Thanks in advance.

Answer 1

If you have only three types you can do.

The columns are to wake as to give a perfect answer, so i don't know if user can have multiple what_id per day, but tis will get you on the right rrack

SELECT
   MIN(what_id) what_id , who_id , event_date,
   SUM(CASE WHEN type = 'CALL' THEN 1 END) total_calls. 
   SUM(CASE WHEN type = 'EMAIL' THEN 1 END) total_emails, 
   SUM(CASE WHEN type = 'OTHER' THEN 1 END) total_OTHER s ,
   COUNT(*) total_all 
FRM mytable
GROUP BY who_id , event_date