mysql转置_将mysql查询行转置为列

I have a simple query that produces the below results:

SELECT month,transporttype,count(transporttype) as loads

from deliveries

group by month,transporttype

I would like to transpose the rows into columns.

I understand mysql does not have pivot functions so a union is required but not 100% sure.

Thanks in advance for the help.

解决方案

You can do it with a crosstab like this -

SELECT

`year`,

`month`,

SUM(IF(`transporttype` = 'inbound', 1, 0)) AS `inbound`,

SUM(IF(`transporttype` = 'LocalPMB', 1, 0)) AS `LocalPMB`,

SUM(IF(`transporttype` = 'Long Distance', 1, 0)) AS `Long Distance`,

SUM(IF(`transporttype` = 'shuttle', 1, 0)) AS `shuttle`,

SUM(IF(`transporttype` = 'export', 1, 0)) AS `export`,

SUM(IF(`transporttype` = 'Extrusions-LongDistance', 1, 0)) AS `Extrusions-LongDistance`,

SUM(IF(`transporttype` = 'Extrusions-Shuttle', 1, 0)) AS `Extrusions-Shuttle`

FROM `deliveries`

GROUP BY `year`, `month`

On a different note, you should move transporttype values to a lookup table and have transporttype_id in this table.