MySQL: Pivot Table - Pivot Rows to Columns Dynamically

In this article, we will see how to implement a pivot table in MySQL. We will do that step by step, so that it becomes clear, what we are doing and why we are doing it.

Sample Data

Use this data if you want to follow along with this tutorial. Here we have data from a few persons collecting some items in different bins.

Table Name: bin_quantity

Table Data:

id	bin	collector	quantity
1	bin 98	Person A	100
2	bin 100	Person A	10
3	bin 100	Person C	10
4	bin 269	Person B	50
5	bin 269	Person A	23
6	bin 269	Person B	5
7	bin 269	Person C	5
8	bin 276	Person B	50
9	bin 276	Person A	50
10	bin 276	Person C	50
11	bin 276	Person B	50
12	bin 276	Person C	50
13	bin 277	Person B	100
14	bin 277	Person A	6

Use the following query to create a table and populate data:

CREATE TABLE `bin_quantity` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bin` varchar(200) DEFAULT NULL,
  `collector` varchar(200) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

insert  into `bin_quantity`(`id`,`bin`,`collector`,`quantity`) values 
(1,'bin 98','Person A',100),
(2,'bin 100','Person A',10),
(3,'bin 100','Person C',10),
(4,'bin 269','Person B',50),
(5,'bin 269','Person A',23),
(6,'bin 269','Person B',5),
(7,'bin 269','Person C',5),
(8,'bin 276','Person B',50),
(9,'bin 276','Person A',50),
(10,'bin 276','Person C',50),
(11,'bin 276','Person B',50),
(12,'bin 276','Person C',50),
(13,'bin 277','Person B',100),
(14,'bin 277','Person A',6);

Expected Output

As a final result, we want to see which person collected how many items for each bin. So the expected result is like this below.

bin	Person A	Person B	Person C
bin 98	100	0	0
bin 100	10	0	10
bin 269	23	55	5
bin 276	50	100	100
bin 277	6	100	0

Steps

Follow the steps below to implement the pivot table dynamically.

Step #1: Initial Attempt

To generate the pivot table data we can use a query like the below which will be the expected result.

SELECT 
	`bin`,
	SUM(CASE WHEN collector = "Person A" THEN quantity ELSE 0 END) AS "Person A",
	SUM(CASE WHEN collector = "Person B" THEN quantity ELSE 0 END) AS "Person B",
	SUM(CASE WHEN collector = "Person C" THEN quantity ELSE 0 END) AS "Person C"
FROM bin_quantity
GROUP BY `bin`

Though this gives the expected result, it has some limitations. Because we are assuming there is three distinct person in the table. So if the data of a new person is added, then we have to change our query.

How to make it dynamic then?

We have to generate that SUM(CASE WHEN collector = “Person A” THEN quantity ELSE 0 END) AS “Person A”, SUM(CASE WHEN collect … part dynamically using SQL.

Step #2: Dynamically Generate the CASE Statement Part

Use the following query to generate the CASE part of the statement.

SET @pivot_sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN collector = "', `collector`, '" THEN quantity ELSE 0 end) AS "', `collector`, '"'
    )
  )
INTO @pivot_sql
FROM
  bin_quantity;

-- Use the following line to check the generated string
-- SELECT @pivot_sql;

And this will give you a string like below, which is stored in @pivot_sql.

SUM(CASE WHEN collector = "Person A" THEN quantity ELSE 0 end) AS "Person A",
SUM(CASE WHEN collector = "Person B" THEN quantity ELSE 0 end) AS "Person B",
SUM(CASE WHEN collector = "Person C" THEN quantity ELSE 0 end) AS "Person C"

Step #3: Generate Full Query

Then the full query can be generated and executed by using the following statements.

SET @pivot_sql = CONCAT('SELECT bin, ', @pivot_sql, ' FROM bin_quantity GROUP BY bin');

PREPARE stmt FROM @pivot_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Full Query for Pivot Table

So the final full query to use is:

SET @pivot_sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN collector = "', `collector`, '" THEN quantity ELSE 0 end) AS "', `collector`, '"'
    )
  )
INTO @pivot_sql
FROM
  bin_quantity;

SET @pivot_sql = CONCAT('SELECT bin, ', @pivot_sql, ' FROM bin_quantity GROUP BY bin');

PREPARE stmt FROM @pivot_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Final Result

Using the query we will get our expected result:

bin	Person A	Person B	Person C
bin 98	100	0	0
bin 100	10	0	10
bin 269	23	55	5
bin 276	50	100	100
bin 277	6	100	0

MySQL: Pivot Table – Pivot Rows to Columns Dynamically