How to implement pivot table output in MySQL

Question

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

P粉154228483 · Answer

My solution is to use 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

P粉366946380 · Answer

This is basically a pivot table.

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

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

renew

Since the link above is no longer available at the moment, I felt the need to provide some additional information for anyone looking for answers to mysql pivot tables here. It does contain a lot 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 suggestions on how to work with pivot tables in sql, to The question raised by peku is an example.

Maybe the link will be restored soon, I will continue to follow it.

Spreadsheet method...

Many people just use spreadsheet tools like MSExcel, OpenOffice or other for 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, scaling 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 wants:

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.

To set conditions, this is more like using a CASE statement. To group by something, use...GROUP BY.

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

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

Some additional links on this topic: