Pivot Query

A database table stores individual records into rows, some times these row data need to be transformed into column data called PIVOT. PIVOT tables are quite common in MICROSOFT EXCEL to display data differently. SQL Server has a built-in PIVOT function. MySQL doesn't provide any PIVOT table to perform this action, but a PIVOT table can be created by GROUP_CONCAT, COUNT or SUM

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE fruit (fruit_id INT, fruit_name VARCHAR(20), fruit_price DECIMAL(10,2), fruit_type VARCHAR(20), PRIMARY KEY (fruit_id));
INSERT INTO fruit (fruit_id, fruit_name, fruit_price, fruit_type) VALUES (1, "Macintosh", 1.1, "apple");
INSERT INTO fruit (fruit_id, fruit_name, fruit_price, fruit_type) VALUES (2, "Fuji", 2.2, "apple");
INSERT INTO fruit (fruit_id, fruit_name, fruit_price, fruit_type) VALUES (3, "Gala", 3.3, "apple");
INSERT INTO fruit (fruit_id, fruit_name, fruit_price, fruit_type) VALUES (4, "Jonagold", 4.4, "apple");
INSERT INTO fruit (fruit_id, fruit_name, fruit_price, fruit_type) VALUES (5, "GrannySmith", 5.5, "apple");

select * from fruit;

SELECT 
  fruit_type,
  GROUP_CONCAT( if(fruit_id=1,fruit_price,NULL) ) AS 'Macintosh',
  GROUP_CONCAT( if(fruit_id=2,fruit_price,NULL) ) AS 'Fuji',
  GROUP_CONCAT( if(fruit_id=3,fruit_price,NULL) ) AS 'Gala',
  GROUP_CONCAT( if(fruit_id=4,fruit_price,NULL) ) AS 'Jonagold',
  GROUP_CONCAT( if(fruit_id=5,fruit_price,NULL) ) AS 'GrannySmith'
FROM fruit
GROUP BY fruit_type;

Pivot table using COUNT function

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE fruit (fruit_id INT, fruit_name VARCHAR(20), fruit_price DECIMAL(10,2), fruit_type VARCHAR(20), PRIMARY KEY (fruit_id));
INSERT INTO fruit (fruit_id, fruit_name, fruit_price, fruit_type) VALUES (1, "Macintosh", 1.1, "apple");
INSERT INTO fruit (fruit_id, fruit_name, fruit_price, fruit_type) VALUES (2, "Fuji", 2.2, "apple");
INSERT INTO fruit (fruit_id, fruit_name, fruit_price, fruit_type) VALUES (3, "Gala", 3.3, "apple");
INSERT INTO fruit (fruit_id, fruit_name, fruit_price, fruit_type) VALUES (4, "Jonagold", 4.4, "apple");
INSERT INTO fruit (fruit_id, fruit_name, fruit_price, fruit_type) VALUES (5, "GrannySmith", 5.5, "apple");

select * from fruit;

SELECT 
  fruit_type,
  COUNT( if(fruit_id=1,fruit_price,NULL) ) AS 'Macintosh',
  COUNT( if(fruit_id=2,fruit_price,NULL) ) AS 'Fuji',
  COUNT( if(fruit_id=3,fruit_price,NULL) ) AS 'Gala',
  COUNT( if(fruit_id=4,fruit_price,NULL) ) AS 'Jonagold',
  COUNT( if(fruit_id=5,fruit_price,NULL) ) AS 'GrannySmith'
FROM fruit
GROUP BY fruit_type;

Pivot table using SUM and CASE function

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE fruit (fruit_id INT, fruit_name VARCHAR(20), fruit_price DECIMAL(10,2), fruit_type VARCHAR(20), PRIMARY KEY (fruit_id));
INSERT INTO fruit (fruit_id, fruit_name, fruit_price, fruit_type) VALUES (1, "Macintosh", 1.1, "apple");
INSERT INTO fruit (fruit_id, fruit_name, fruit_price, fruit_type) VALUES (2, "Fuji", 2.2, "apple");
INSERT INTO fruit (fruit_id, fruit_name, fruit_price, fruit_type) VALUES (3, "Gala", 3.3, "apple");
INSERT INTO fruit (fruit_id, fruit_name, fruit_price, fruit_type) VALUES (4, "Jonagold", 4.4, "apple");
INSERT INTO fruit (fruit_id, fruit_name, fruit_price, fruit_type) VALUES (5, "GrannySmith", 5.5, "apple");

select * from fruit;

SELECT 
  fruit_type,
  SUM( CASE WHEN fruit_id=1 THEN fruit_price ELSE NULL END) AS 'Macintosh',
  SUM( CASE WHEN fruit_id=2 THEN fruit_price ELSE NULL END) AS 'Fuji',
  SUM( CASE WHEN fruit_id=3 THEN fruit_price ELSE NULL END ) AS 'Gala',
  SUM( CASE WHEN fruit_id=4 THEN fruit_price ELSE NULL END ) AS 'Jonagold',
  SUM( CASE WHEN fruit_id=5 THEN fruit_price ELSE NULL END ) AS 'GrannySmith'
FROM fruit
GROUP BY fruit_type;

Combine multiple rows to create sing Pivot row

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE fruit (fruit_id INT, fruit_name VARCHAR(20), fruit_price DECIMAL(10,2), fruit_type VARCHAR(20), PRIMARY KEY (fruit_id));
INSERT INTO fruit (fruit_id, fruit_name, fruit_price, fruit_type) VALUES (1, "Macintosh", 1.1, "apple");
INSERT INTO fruit (fruit_id, fruit_name, fruit_price, fruit_type) VALUES (2, "Macintosh", 2.2, "apple");
INSERT INTO fruit (fruit_id, fruit_name, fruit_price, fruit_type) VALUES (3, "Fuji", 2.2, "apple");
INSERT INTO fruit (fruit_id, fruit_name, fruit_price, fruit_type) VALUES (4, "Gala", 3.3, "apple");
INSERT INTO fruit (fruit_id, fruit_name, fruit_price, fruit_type) VALUES (5, "Jonagold", 4.4, "apple");
INSERT INTO fruit (fruit_id, fruit_name, fruit_price, fruit_type) VALUES (6, "GrannySmith", 5.5, "apple");

select * from fruit;

SELECT 
  fruit_type,
  SUM( CASE WHEN fruit_id=1 or fruit_id=2 THEN fruit_price ELSE NULL END) AS 'Macintosh',
  SUM( CASE WHEN fruit_id=2 THEN fruit_price ELSE NULL END) AS 'Fuji',
  SUM( CASE WHEN fruit_id=3 THEN fruit_price ELSE NULL END ) AS 'Gala',
  SUM( CASE WHEN fruit_id=4 THEN fruit_price ELSE NULL END ) AS 'Jonagold',
  SUM( CASE WHEN fruit_id=5 THEN fruit_price ELSE NULL END ) AS 'GrannySmith'
FROM fruit
GROUP BY fruit_type;

follow us on

YouTube IconLinkedIn Icon