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

Question

If I have a MySQL table like this: company_nameactionpagecount----------------------------------CompanyAPRINT3CompanyAPRINT2CompanyAPRINT3CompanyBEMAILCompanyBPRINT2CompanyBPRINT2CompanyBPRINT1CompanyAPRINT3 Is it possible to run a MySQL

P粉696891871 · Answer

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

P粉608647033 · Answer

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: