How to generate the output of a pivot table in MySQL?

Question

If I have a MySQL table like this:

company_name action pagecount
----------------------------------
COMPANY A PRINT 3
COMPANY A PRINT 2
COMPANY A PRINT 3
Company BEMAIL
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 page PRINT 2 pages PRINT 3 pages
-------------------------------------------------- ----------
CompanyA 0 0 1 3
CompanyB 1 1 2 0

The idea is that pagecount may change, so the number of columns in the output should reflect this, one column for each action/pagecount , and then count the number of hits for each company_name. I'm not sure if this is called a pivot table, but someone suggested this

Answer 1

My solution is to use T-SQL without any pivot:

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

Answer 2

This is basically a pivot table.

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

I recommend reading this article and adapting this solution to your needs.

renew

Since the link above is currently no longer available, I feel obligated to provide some additional information for anyone looking for answers to mysql pivot tables here. It does have a ton of information, and I won't copy everything here (not to mention I don't want to copy their vast knowledge), but I will give some advice on how to work with pivot tables using SQL, and first Example of peku that asked the question.

Maybe the link will come back soon, I'll keep an eye out.

Spreadsheet method...

Many people just use spreadsheet tools like MSExcel, OpenOffice or other to accomplish this purpose. This is a valid solution, just copy the data there and use the tools provided by the GUI to solve the problem.

But... this is not the crux of the problem, and may even lead to some shortcomings, such as how to import data into spreadsheets, scalability issues, etc.

SQL way...

Suppose his table looks 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 at the table he/she expects:

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

The

line (EMAIL, PRINT x pages) represents the condition. The main grouping is by company_name.

In order to set conditions, it is more appropriate to use the CASE statement. To group by something, use...GROUP BY.

The basic SQL to provide this pivot table might be 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 results very quickly. The main disadvantage of this approach is that the more rows you need in the pivot table, the more conditions you need to define in the SQL statement.

This can also be solved, so people tend to use prepared statements, routines, counters, etc.

Some additional links on this topic:

• http://anothermysqldba.blogspot.de/2013/06/pivot-tables-example-in-mysql.html
• http://www.codeproject.com/Articles/363339/Cross-Tabulation-Pivot-Tables-with-MySQL
• http://datacharmer.org/downloads/pivot_tables_mysql_5.pdf
• https://codingsight.com/pivot-tables-in-mysql/