How to return pivot table output in MySQL?

Question

If I have a MySQL table that looks 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 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 count should reflect this, for each action/pagecount For one column, then the number of clicks for each company_name. I'm not sure if this is called a pivot table, but has anyone suggested this?

Answer 1

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

Answer 2

This is basically a pivot table.

A good tutorial on how to achieve 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

After the link above is currently no longer available, I felt the need to provide some additional information for anyone searching here for mysql hub answers. It does have a ton of information, I won't put all of it here (or even more, as I just don't want to replicate their vast knowledge), but I will provide some advice on how to deal with pivots in general in sql Method table, taking peku who first asked the question as an example.

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

Spreadsheet method...

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

But... this is not a problem, it may even lead to some disadvantages, such as problems with how to get the data into the spreadsheet, scaling, etc.

SQL way...

Given that his form 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 view the form 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

lines (email, print x pages) are similar to conditions. The main grouping is by company_name.

In order to set conditions, you need to use the CASE-statement. To group by something, use...GROUP BY.

Basic SQL to provide this pivot 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 in the PivotTable, the more conditions you need to define in the SQL statement.

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

Some other links on this topic:

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