Transpose rows to columns MySQL

Question

I have query that provides count of employee joined month wise across all cities. (In MySQL)

SELECT SUBSTR(`JoiningDate`,4,3) as Month,
 Location,
 Count(*) as `EmployeeCount` 
 FROM `data` 
 WHERE Location IN ('NYC','SFO','LA')   
 GROUP BY Month,Location

The output is in this format -

| Month|Location| Count|
-----------------------
|  Jan | NYC    |   100|
|  Jan | SFO    |   500|
|  Jan | LA     |   200|
|  Feb | NYC    |   100|
|  Feb | SFO    |   400|
|  Feb | LA     |   500|

How would i be able to transpose the data in a format, it produces columns for each city with relevant count per month? Something like below -

| Month|NYC |SFO | LA |
-----------------------
|  Jan |100 |500 |200 |

|  Feb |100 |400 |500 |

|  Mar |300 |300 |650 |

Gurwinder Singh · Accepted Answer · 2017-03-23T18:39:09.733

0

You can use conditional aggregation:

select SUBSTR(`JoiningDate`, 4, 3) as Month,
    sum(location = 'NYC') as `NYC`,
    sum(location = 'SFO') as `SFO`,
    sum(location = 'LA' ) as `LA`
from `data`
where Location in ('NYC', 'SFO', 'LA')
group by SUBSTR(`JoiningDate`, 4, 3)

It uses the fact that true evaluates to 1 and false evaluates to 0 in MySQL which you can sum to find the required counts.

edited Mar 23 '17 at 18:39

answered Mar 23 '17 at 18:23

Gurwinder Singh

38,557
6
51
76

Thanks @ GurV, how about the scenario when the list of city is not known? Meaning it isnt a finite list. – Chaitanya Mar 23 '17 at 18:27
@Chaitanya - In that case it's recommended to handle this in application code. Another (ugly) way is using dynamic SQL. – Gurwinder Singh Mar 23 '17 at 18:28
yea makes sense to push the logic to application where looping and such is simpler. Thanks! – Chaitanya Mar 23 '17 at 18:38

Transpose rows to columns MySQL

1 Answers1