mysql query to output rows to columns

Question

I have the below query that works 100%.

SELECT
transporttype,
concat(MONTHNAME(STR_TO_DATE(month, '%m')), ' ', year) AS `month`,
round(sum(cost),0) AS cost
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

This outputs the results in 1 column as below:

enter image description here

How can I manipulate the query so that the output is in columns, so I can graph it. desired output is like:

enter image description here

Help appreciated as always,

Updated to match possible answer from Oscar Pérez

enter image description here

Nothing visible here ... Where is 1 column as below: and output is like? — Sahal
– Sahal, Commented Jan 25, 2013 at 10:43
It is a PIVOT table for your query. Here is a good explanation how to do it. — valex
– valex, Commented Jan 25, 2013 at 10:52

Oscar Pérez · Accepted Answer · 2013-01-25 11:52:01Z

1

You could use the LEFT JOIN operator. For example:

SELECT s0.month, 
       s1.cost as Inbound, 
       s2.cost as LocalPMB,
       s3.cost as Shuttle,    
       s4.cost as LongDistance
  FROM (
         SELECT 1 as month
          UNION
         SELECT 2 as month
          UNION
         SELECT 3 as month
          UNION
         SELECT 4 as month
          UNION
         SELECT 5 as month
          UNION
         SELECT 6 as month
          UNION
         SELECT 7 as month
          UNION
         SELECT 8 as month
          UNION
         SELECT 9 as month
          UNION
         SELECT 10 as month
          UNION
         SELECT 11 as month
          UNION
         SELECT 12 as month
       ) as s0
LEFT JOIN
       (
         SELECT month,
                round(sum(cost),0) AS cost
           FROM v2ReportingTable
          WHERE urgent='no'
            AND transporttype='Inbound'
       GROUP BY month
       ) as s1 on s0.month=s1.month
LEFT JOIN
       (
         SELECT month,
                round(sum(cost),0) AS cost
           FROM v2ReportingTable
          WHERE urgent='no'
            AND transporttype='LocalPMB'
       GROUP BY month
       ) as s2 on s0.month=s2.month
LEFT JOIN
       (
         SELECT month,
                round(sum(cost),0) AS cost
           FROM v2ReportingTable
          WHERE urgent='no'
            AND transporttype='Shuttle'
       GROUP BY month
       ) as s3 on s0.month=s3.month
LEFT JOIN
       (
         SELECT month,
                round(sum(cost),0) AS cost
           FROM v2ReportingTable
          WHERE urgent='no'
            AND transporttype='Long Distance'
       GROUP BY month
       ) as s4 on s0.month=s4.month
ORDER BY month

edited Jan 25, 2013 at 11:52

answered Jan 25, 2013 at 10:50

Oscar Pérez

4,3971 gold badge20 silver badges38 bronze badges

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

4 Comments

Smudger Over a year ago

Thanks Oscar, works well but not completely. if month 2,3,4, does not have data, the corresponding data for month 1 is not present as the joined column has no data? how can we force it to show all data from month 1 and only what data is available for month 2, month 3 etc. any ideas? outer join? Thanks, Ryan

Oscar Pérez Over a year ago

Edited response. Now you'll get 12 rows and the corresponding columns. Every column will be empty for the months that it has no data

Smudger Over a year ago

Hi, this only shows values for Inbound. as there are no values for the other 'transport types' it displays blank as per updated image on question...

Oscar Pérez Over a year ago

Hi, I created a Fiddle to show this query with sample data... Just for curiosity: are not its results correct? You can see the Fiddle at: sqlfiddle.com/#!2/2c5ad/1

Smudger · Accepted Answer · 2013-01-28 14:11:49Z

as per @JW, thanks for your assistance.

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

mysql query to output rows to columns

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related