Source Code : MySQL pivot table

MySQL pivot table

If I have a MySQL table looking something like this:

company_name    action  pagecount
-------------------------------
Company A       PRINT   3
Company A       PRINT   2
Company A       PRINT   3
Company B       EMAIL   
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 amount should reflect that, one column for each action/pagecount pair and then number of hits per company_name. I'm not sure if this is called a pivot table but someone suggested that?

The SQL way...

Given his table looks something like this:

<code>CREATETABLE`test_pivot`(`pid` bigint(20)NOTNULL AUTO_INCREMENT,`company_name` varchar(32)DEFAULTNULL,`action` varchar(16)DEFAULTNULL,`pagecount` bigint(20)DEFAULTNULL,PRIMARYKEY(`pid`)) ENGINE=MyISAM;</code>

Now look into his/her desired table:

<code>company_name    EMAIL   PRINT1 pages   PRINT2 pages   PRINT3 pages
-------------------------------------------------------------
CompanyA        0013
CompanyB        1120</code>

The rows (EMAIL, PRINT x pages) resemble conditions. The main grouping is by company_name.

In order to set up the conditions this rather shouts for using the CASE-statement. In order to group by something, well, use ... GROUP BY.

The basic SQL providing this pivot can look something like this:

<code>SELECT  P.`company_name`,
    COUNT(CASEWHEN P.`action`='EMAIL'THEN1ELSENULLEND)AS'EMAIL',
    COUNT(CASEWHEN P.`action`='PRINT'AND P.`pagecount`='1'THEN P.`pagecount`ELSENULLEND)AS'PRINT 1 pages',
    COUNT(CASEWHEN P.`action`='PRINT'AND P.`pagecount`='2'THEN P.`pagecount`ELSENULLEND)AS'PRINT 2 pages',
    COUNT(CASEWHEN P.`action`='PRINT'AND P.`pagecount`='3'THEN P.`pagecount`ELSENULLEND)AS'PRINT 3 pages'FROM    test_pivot P
GROUPBY P.`company_name`;</code>

This should provide the desired result very fast. The major downside for this approach, the more rows you want in your pivot table, the more conditions you need to define in your SQL statement.

This can be dealt with, too, therefore people tend to use prepared statements, routines, counters and such.

Some additional links about 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
• http://buysql.com/mysql/14-how-to-automate-pivot-tables.html

My solution in T-SQL without any pivots:

<code>select
CompanyName,  
SUM(CASEWHEN(action='EMAIL')THEN1ELSE0END)AS Email,
SUM(CASEWHEN(action='PRINT'AND pagecount=1)THEN1ELSE0END)AS Print1Pages,
SUM(CASEWHEN(action='PRINT'AND pagecount=2)THEN1ELSE0END)AS Print2Pages,
SUM(CASEWHEN(action='PRINT'AND pagecount=3)THEN1ELSE0END)AS Print3Pages

from Company
groupby CompanyName</code>

select t3.name, sum(t3.prod_A) as Prod_A, sum(t3.prod_B) as Prod_B, sum(t3.prod_C) as  Prod_C, sum(t3.prod_D) as Prod_D, sum(t3.prod_E) as Prod_E  
from
(select t2.name as name,
case when t2.prodid = 1 then t2.counts
else 0 end  prod_A,

case when t2.prodid = 2 then t2.counts
else 0 end prod_B,

case when t2.prodid = 3 then t2.counts
else 0 end prod_C,

case when t2.prodid = 4 then t2.counts
else 0 end prod_D,

case when t2.prodid = "5" then t2.counts
else 0 end prod_E

from
(SELECT partners.name as name, sales.products_id as prodid, count(products.name) as counts
FROM test.sales left outer join test.partners on sales.partners_id = partners.id
left outer join test.products on sales.products_id = products.id
where sales.partners_id = partners.id and sales.products_id = products.id group by partners.name, prodid) t2) t3

group by t3.name ;