Mimicking an excel pivot in SQL

Question

Been a long time since I've been in the DB world as I'm now primarily a front end developer.

I have a sql db table that looks like this :

Email          | State           | Status
___________________________________________________

bob@email.com    Colorado          1
jim@email.com    North Carolina    2
sarah@email.com  Colorado          3
fred@email.com   North Carolina    2

I need a query that makes a table that looks like this :

State          | 1          | 2          | 3          | Total 
_________________________________________________________________ 
Colorado         1            0            1            2
North Carolina   0            2            0            2

Any help is appreciated

see https://stackoverflow.com/questions/7674786/mysql-pivot-table — M. Rezaeyan, Aug 18 '17 at 19:25
Could you try this by yourself before we help you ? If you have provide us the query with the output of the query and what the problem with the output is. Also provide after the output the desired ouput — Noob, Aug 18 '17 at 19:39

score 1 · Accepted Answer · answered Aug 18 '17 at 19:46

1

select 
State,
sum(case when Status=1 then 1 else 0 end) '1',
sum(case when Status=2 then 1 else 0 end) '2',
sum(case when Status=3 then 1 else 0 end) '3',
sum(case when Status>0 then 1 else 0 end) 'total'
from Your_Table
group by State

answered Aug 18 '17 at 19:46

Javlon Ismatov

194
12

Mimicking an excel pivot in SQL

1 Answers1