Transpose mysql query rows into columns

Question

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.

possible duplicate of How to pivot a MySQL entity-attribute-value schema — Brad Christie
– Brad Christie, Commented Mar 13, 2012 at 14:08
Go through this [stackoverflow.com/questions/1851781/… might help you. — Java
– Java, Commented Mar 13, 2012 at 14:23

user1191247 · Accepted Answer · 2012-03-13 15:28:31Z

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.

edited Mar 13, 2012 at 15:28

answered Mar 13, 2012 at 14:42

user1191247

13k2 gold badges25 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DRapp Over a year ago

@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.

user1191247 Over a year ago

@DRapp thanks for the note of caution. I will modify my query to include the year.

Collectives™ on Stack Overflow

Transpose mysql query rows into columns

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related