Login/Register
Transpose mysql query rows into columns
Asked Answered
A

1

5

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.

Alaska answered 13/3, 2012 at 14:5 Comment(2)
possible duplicate of How to pivot a MySQL entity-attribute-value schemaMassimo
Go through this [#1852281 might help you.Lutenist
O
8

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.

Officiary answered 13/3, 2012 at 14:42 Comment(2)
@RyanSmith (note), This answer should do it for you, however, you might want to consider adding the YEAR as an additional group by in case you span year activity. You wouldn't want false numbers rolled into one year vs another.Sy
@Sy thanks for the note of caution. I will modify my query to include the year.Officiary

© 2022 - 2024 — McMap. All rights reserved.