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.

Smudger
  • 10,451
  • 29
  • 104
  • 179
  • 1
    possible duplicate of [How to pivot a MySQL entity-attribute-value schema](http://stackoverflow.com/questions/649802/how-to-pivot-a-mysql-entity-attribute-value-schema) – Brad Christie Mar 13 '12 at 14:08
  • 1
    Go through this [http://stackoverflow.com/questions/1851781/transpose-a-row-into-columns-with-mysql-without-using-unions] might help you. – Java Mar 13 '12 at 14:23

1 Answers1

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.

user1191247
  • 10,808
  • 2
  • 22
  • 32
  • 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. – DRapp Mar 13 '12 at 15:17
  • 1
    @DRapp thanks for the note of caution. I will modify my query to include the year. – user1191247 Mar 13 '12 at 15:27