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:

idbincollectorquantity
1bin 98Person A100
2bin 100Person A10
3bin 100Person C10
4bin 269Person B50
5bin 269Person A23
6bin 269Person B5
7bin 269Person C5
8bin 276Person B50
9bin 276Person A50
10bin 276Person C50
11bin 276Person B50
12bin 276Person C50
13bin 277Person B100
14bin 277Person A6

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.

binPerson APerson BPerson C
bin 9810000
bin 10010010
bin 26923555
bin 27650100100
bin 27761000

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:

binPerson APerson BPerson C
bin 9810000
bin 10010010
bin 26923555
bin 27650100100
bin 27761000

Leave a Comment


The reCAPTCHA verification period has expired. Please reload the page.