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
___________________________________________________

[email protected]    Colorado          1
[email protected]    North Carolina    2
[email protected]  Colorado          3
[email protected]   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