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

Question

If I have a MySQL table like this: company_nameactionpagecount----------------------------------CompanyAPRINT3CompanyAPRINT2CompanyAPRINT3CompanyBEMAILCompanyBPRINT2CompanyBPRINT2CompanyBPRINT1CompanyAPRINT3Isitpossible

P粉461599845 · Answer

My solution is to use T-SQL instead of any pivot table:

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粉574268989 · 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 adjust it to your own needs.

UPDATE

Since the link above is currently no longer available, I felt it necessary to provide some additional information for anyone looking for answers to MySQL Pivot Tables here. This link really has a lot of information, I'm not going to copy it all (and I don't want to copy their wealth of knowledge), but I will give some advice on how to work with pivot tables in SQL, with peku Take the question posed as an example.

Maybe the link will be restored soon, I will keep an eye on it.

The spreadsheet way...

Many people just use tools like MSExcel, OpenOffice or other spreadsheet tools to accomplish this task. 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 disadvantages, 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 expected table:

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) is similar to a condition. The main grouping is by company name.

In order to set conditions, it is more suitable to use the CASE statement. In order to group by something, you can use GROUP BY.

The basic SQL statement that provides this pivot table might look 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 want to have in the pivot table, the more conditions you need to define in the SQL statement.

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

Here are some additional links on this topic: