How to Create Pivot Tables in MySQL Using CASE Statements and GROUP BY?

Generating Pivot Tables in MySQL: A Guide

Pivot tables are invaluable for data summarization, allowing you to aggregate data across multiple dimensions. This guide demonstrates how to create pivot tables in MySQL using CASE statements and GROUP BY.

Illustrative Example:

Consider this sample MySQL table:

company_name	action	pagecount
Company A	PRINT	3
Company A	PRINT	2
Company A	PRINT	3
Company B	EMAIL	NULL
Company B	PRINT	2
Company B	PRINT	2
Company B	PRINT	1
Company A	PRINT	3

The following SQL query constructs a pivot table from this data:

<code class="language-sql">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`;</code>

This query yields the following pivot table:

company_name	EMAIL	PRINT 1 pages	PRINT 2 pages	PRINT 3 pages
CompanyA	0	0	1	3
CompanyB	1	1	2	0

Further Learning:

For a deeper understanding of MySQL pivot tables, explore these resources:

• Artful Software: Pivot Queries in SQL
• Another MySQL DBA: Pivot Tables Example in MySQL
• CodeProject: Cross-Tabulation (Pivot Tables) with MySQL
• Coding Sight: Pivot Tables in MySQL

The above is the detailed content of How to Create Pivot Tables in MySQL Using CASE Statements and GROUP BY?. For more information, please follow other related articles on the PHP Chinese website!