Pivot a table in MySQL – Tutorial

Stratos Provatopoulos

I am currently working as a web developer in Greece for an advertising agency but I consider myself to be an all around technology enthusiast. I like working both front end and back end, so I guess that makes me a full stack developer. You can find me at , Twitter, Linkedin and Facebook

25 Responses

  1. derek says:

    Thanks for this, very helpful!
    It looks like I’m able to skip Step 4 all together if I just add an ‘else’ clause in Step 2, so:
    …case when Item_Type = “Computer” then Item_Amount else 0 end as Computer,…

    Seems to put 0 where my nulls would have been and turn out the same result. Am I thinking about that right?

  2. strapro says:

    Yeah exactly!

    As long as you want the query to return numerics this is fine. Just keep in mind that in some cases null is preferable to zero. For this example your way is actually better!

  3. jake says:

    Is it possible to pivot without knowing/hardcoding the items? We could end up with so many columns. Cant think how to do it.

  4. strapro says:

    Yes it is possible! It involves prepared statements to dynamically get the items names beforehand and then feed the result to pivoting query.

    Check out this post:
    http://stratosprovatopoulos.com/web-development/mysql/pivot-table-with-dynamic-columns/

  5. Mr.Black says:

    Great Articles! Thanks for your super E.A.S.Y explanation!!

  6. Mahesha B says:

    Super, Thanks very help full. All the best

  7. Great article! Little typo at the end:
    On a closing note it is worth mentioning that this approach, is meant for scenarios where the columns of interest are predefined and static. If we want to pivot a table whose values are more dynanic (<- dynamic) and can be changed at runtime, a different approach using prepared statements is more suitable.

  8. strapro says:

    Fixed! Thanks a lot!

  9. grails says:

    Is pivot similar to concept of inverse table, suitable for faster queries?

    • strapro says:

      Well if you optimize the table correctly it should be fast enough even in big data sets. However it will always add time to the total execution time. I suppose that if speed is of the utmost importance then you could pivot the table, store the result in a temporary/cache table and perform queries on that dataset. Data will have to be re-validated and re-cached every now and then, depending on the scenario, but queries will be a lot faster.

  10. Selçuk Okutan says:

    Thank you for full complete and clear information. Teşekkürler.

  11. Maxwell says:

    When running the sql that has all the SUM() functions… how do you combine the fields if it contains text? Running SUM() on it.. returns a 0.

    • Maxwell says:

      I was able to get the results I needed using: GROUP_CONCAT([fieldname] SEPARATOR ‘, ‘) AS [fieldname]

      • Randy says:

        Brilliant mate!

        To clarify for everybody if you have to pivot a text table in mysql:

        instead of

        sum(Computer) as Computer,
        sum(Monitor) as Monitor,
        sum(Software) as Software

        use

        GROUP_CONCAT(Computer) AS Computer,
        GROUP_CONCAT(Monitor) AS Monitor,
        GROUP_CONCAT(Software) AS Software

        Thank you very much Stratos!
        You might want to write an article specifically with this modifications, because I couldn’t find anything else specific from a google, search and I am sure a lot of people need it 😉

  12. Tom says:

    Thank you for this excellent tutorial – exactly what I am looking for!

    But I would like to use only strings in my table, like so:

    insert into User_Items values
    (‘Alison’, ‘2014’, ‘Tech2’),
    (‘Alison’, ‘2013’, ‘Tech1’),
    (‘Alison’, ‘2012’, ‘Tech1’),
    (‘Jason’, ‘2014’, ‘Ops’),
    (‘Jason’, ‘2013’, ‘Ops’),
    (‘Jason’, ‘2012’, ‘Ops’);

    which would then show

    2014 2013 2012
    Alison Tech2 Tech1 Tech1
    Jason Ops Ops Ops

    I understand the ‘sum’ part (which will also come in handy when I am working with values), but not sure how to alter that for strings only?

    Any help very much appreciated – excellent site!!

    Thanks!

  13. Felix says:

    Thank you, very helpful!

  14. Omer says:

    Thanks a lot for sharing this. Hope future MySQL releases add the a built-in function/clause as it is the case in SQL Server.
    I would like to know if it is possible to add a column Total which will sum all the rows that became columns
    ArticleID Description TotalSales Quarter1 Quarter2 Quarter3

  15. s1ck-b0y says:

    ¡Gracias che! 😀

  16. Jonathan says:

    Super! I’d been looking for this for a long time.

  17. Nirali says:

    Awsome…Thanks a lot

  18. JRO says:

    Thank you so muuuuuch!!! (y) (y) (y) awesome! I have been looking for this for the whole week. Ez!!!!!!!!!!!!!!! keep sharing and helping others

  1. February 20, 2014

    […] pivot", here are just a few links: MySQL/Pivot table – Wikibooks, open books for an open world Pivot a table in MySQL – Tutorial – Stratos Blog […]

  2. May 27, 2014

    […] Tutorial on how to pivot a table in MySQL using views and aggregate functions. This process is also referred as spinning rows into columns.  […]

  3. March 10, 2015

    […] details on that. You will need to make use of the PIVOT command to get the result you need. Check here for […]

Leave a Reply

Your email address will not be published. Required fields are marked *