SQL select multiple values in single column into multiple columns

I’d like to select from a single table with a simple set of data, but somehow transpose the type column into multiple columns using a simple select statement.

An example of the data I’m working with:

id date type
1 2022/06/01 1
2 2022/06/01 2
3 2022/06/01 3
4 2022/06/01 1
5 2022/06/01 2
6 2022/06/01 3

What I am hoping to achieve, using SQL only:

date_format(%y-%m-%d) SMS Email Online
2022/06/01 2 2 2

Any help will be greatly appreciated.

Best regards,
Joel

>Solution :

If 1 means SMS, 2 means Email, and 3 means Online, then the query can look like:

select
  date,
  sum(case when type = 1 then 1 else 0 end) as sms,
  sum(case when type = 2 then 1 else 0 end) as email,
  sum(case when type = 3 then 1 else 0 end) as online
from t
group by date

Leave a Reply