Categories
mysql pivot sql

How can I return pivot table output in MySQL?

369

If I have a MySQL table looking something like this:

company_name    action  pagecount
-------------------------------
Company A       PRINT   3
Company A       PRINT   2
Company A       PRINT   3
Company B       EMAIL   
Company B       PRINT   2
Company B       PRINT   2
Company B       PRINT   1
Company A       PRINT   3

Is it possible to run a MySQL query to get output like this:

company_name    EMAIL   PRINT 1 pages   PRINT 2 pages   PRINT 3 pages
-------------------------------------------------------------
CompanyA        0       0               1               3
CompanyB        1       1               2               0

The idea is that pagecount can vary so the output column amount should reflect that, one column for each action/pagecount pair and then number of hits per company_name. I’m not sure if this is called a pivot table but someone suggested that?

6

  • 4

    It’s called pivoting and it’s much, much quicker to do this transformation outside of SQL.

    – N.B.

    Oct 6, 2011 at 13:22

  • 1

    Excel rips through things like this, it’s really difficult in MySQL as there is no “CROSSTAB” operator 🙁

    – Dave Rix

    Oct 6, 2011 at 13:35

  • Yes it’s currently done by hand in Excel and we are trying to automate it.

    – peku

    Oct 6, 2011 at 13:59

  • @N.B. Is it advised to perform it in our application layer or it is just simpler to do it there?

    Sep 28, 2015 at 13:17


  • 1

    @giannischristofakis – it really depends on what you and your coworkers deem simpler. Technology caught up quite a bit since I posted the comment (4 years) so it’s totally up to what you feel is better – be it in application or SQL. For example, at my work we deal with similar problem but we’re combining both SQL and in-app approach. Basically, I can’t help you other than giving opinionated answer and that’s not what you need 🙂

    – N.B.

    Sep 28, 2015 at 13:46

274

This basically is a pivot table.

A nice tutorial on how to achieve this can be found here: http://www.artfulsoftware.com/infotree/qrytip.php?id=78

I advise reading this post and adapt this solution to your needs.

Update

After the link above is currently not available any longer I feel obliged to provide some additional information for all of you searching for mysql pivot answers in here. It really had a vast amount of information, and I won’t put everything from there in here (even more since I just don’t want to copy their vast knowledge), but I’ll give some advice on how to deal with pivot tables the sql way generally with the example from peku who asked the question in the first place.

Maybe the link comes back soon, I’ll keep an eye out for it.

The spreadsheet way…

Many people just use a tool like MSExcel, OpenOffice or other spreadsheet-tools for this purpose. This is a valid solution, just copy the data over there and use the tools the GUI offer to solve this.

But… this wasn’t the question, and it might even lead to some disadvantages, like how to get the data into the spreadsheet, problematic scaling and so on.

The SQL way…

Given his table looks something like this:

CREATE TABLE `test_pivot` (
  `pid` bigint(20) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(32) DEFAULT NULL,
  `action` varchar(16) DEFAULT NULL,
  `pagecount` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`pid`)
) ENGINE=MyISAM;

Now look into his/her desired table:

company_name    EMAIL   PRINT 1 pages   PRINT 2 pages   PRINT 3 pages
-------------------------------------------------------------
CompanyA        0       0               1               3
CompanyB        1       1               2               0

The rows (EMAIL, PRINT x pages) resemble conditions. The main grouping is by company_name.

In order to set up the conditions this rather shouts for using the CASE-statement. In order to group by something, well, use … GROUP BY.

The basic SQL providing this pivot can look something like this:

SELECT  P.`company_name`,
    COUNT(
        CASE 
            WHEN P.`action`='EMAIL' 
            THEN 1 
            ELSE NULL 
        END
    ) AS 'EMAIL',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '1' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 1 pages',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '2' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 2 pages',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '3' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 3 pages'
FROM    test_pivot P
GROUP BY P.`company_name`;

This should provide the desired result very fast. The major downside for this approach, the more rows you want in your pivot table, the more conditions you need to define in your SQL statement.

This can be dealt with, too, therefore people tend to use prepared statements, routines, counters and such.

Some additional links about this topic:

4

102

My solution is in T-SQL without any pivots:

SELECT
    CompanyName,  
    SUM(CASE WHEN (action='EMAIL') THEN 1 ELSE 0 END) AS Email,
    SUM(CASE WHEN (action='PRINT' AND pagecount=1) THEN 1 ELSE 0 END) AS Print1Pages,
    SUM(CASE WHEN (action='PRINT' AND pagecount=2) THEN 1 ELSE 0 END) AS Print2Pages,
    SUM(CASE WHEN (action='PRINT' AND pagecount=3) THEN 1 ELSE 0 END) AS Print3Pages
FROM 
    Company
GROUP BY 
    CompanyName

4

  • 2

    This works for me even on PostgreSQL. I prefer this method than using the crosstab extension on Postgres as this is cleaner

    – itsols

    Oct 10, 2014 at 3:06

  • 7

    “My solution is in T-SQL without any pivots:” Not only SQL Server it should work on most database vendors which follows the ANSI SQL standards. Note that SUM() can only work with numeric data if you ned to pivot strings you will have to use MAX()

    Mar 25, 2019 at 16:25


  • 4

    I think the CASE is unnesesary in SUM(CASE WHEN (action='PRINT' AND pagecount=1) THEN 1 ELSE 0 END), you can just do SUM(action='PRINT' AND pagecount=1) since the condition will be converted to 1 when true and 0 when false

    – kajacx

    Mar 2, 2020 at 8:04

  • 2

    @kajacx yes, though it’s needed on database that don’t have that sort of Boolean manipulation. Given a choice between a “longer syntax that works on all dB” and a “shorter syntax that only works on …” I would pick the former

    May 10, 2020 at 6:53

82

For MySQL you can directly put conditions in SUM() function and it will be evaluated as Boolean 0 or 1 and thus you can have your count based on your criteria without using IF/CASE statements

SELECT
    company_name,  
    SUM(action = 'EMAIL')AS Email,
    SUM(action = 'PRINT' AND pagecount = 1)AS Print1Pages,
    SUM(action = 'PRINT' AND pagecount = 2)AS Print2Pages,
    SUM(action = 'PRINT' AND pagecount = 3)AS Print3Pages
FROM t
GROUP BY company_name

DEMO

4