convert mysql output from rows to columns

Question

I have the MySQL query:

SELECT
transporttype,
concat(MONTHNAME(STR_TO_DATE(month, '%m')), ' ', year) AS `month`,
count(loadnumber) AS loads
FROM v2ReportingTable
WHERE (transporttype not in ('Extrusions-LongDistance','Extrusions-Shuttle') and urgent='no')
GROUP BY (concat(MONTHNAME(STR_TO_DATE(month, '%m')),' ',year)),transporttype
ORDER BY (concat(MONTHNAME(STR_TO_DATE(month, '%m')),' ',year)), transporttype

that produces the below output:

enter image description here

I want to display the results in column format, as below: enter image description here

How can I modify my query to display the results as such.

Assistance is appreciated as always

John Woo · Accepted Answer · 2013-01-28 13:52:33Z

SELECT  `month`,
        MAX(CASE WHEN transporttype = 'Inbound' THEN loads ELSE NULL END) `Inbound`,
        MAX(CASE WHEN transporttype = 'LocalGauteng' THEN loads ELSE NULL END) `LocalGauteng`,
        MAX(CASE WHEN transporttype = 'LongDistance' THEN loads ELSE NULL END) `LongDistance`,
        MAX(CASE WHEN transporttype = 'Shuttle-company1' THEN loads ELSE NULL END) `Shuttle-company1`,
        MAX(CASE WHEN transporttype = 'Shuttle-company2' THEN loads ELSE NULL END) `Shuttle-company2`,
        MAX(CASE WHEN transporttype = 'stores' THEN loads ELSE NULL END) `stores`,
        MAX(CASE WHEN transporttype = 'returns' THEN loads ELSE NULL END) `returns`,
        MAX(CASE WHEN transporttype = 'localkzn' THEN loads ELSE NULL END) `localkzn`
FROM
    (
        SELECT  transporttype,
                CONCAT(MONTHNAME(STR_TO_DATE(month, '%m')), ' ', year) AS `month`,
                COUNT(loadnumber) AS loads
        FROM    v2ReportingTable
        WHERE   transporttype not in ('Extrusions-LongDistance','Extrusions-Shuttle') AND
                urgent='no'
        GROUP BY (concat(MONTHNAME(STR_TO_DATE(month, '%m')),' ',year)), transporttype
    ) aa
GROUP BY `month`

Collectives™ on Stack Overflow

convert mysql output from rows to columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related