Chaitanya Chaitanya -4 years ago 78
MySQL Question

Transpose rows to columns MySQL

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 |

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download