Knowledge Walls
John Peter
Pune, Maharashtra, India
How to Create dynamic pivot query in mysql with Example
27836 Views
Pivot table structure
CREATE TABLE country_wise_population
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
country_name VARCHAR(50) NOT NULL,
state_name VARCHAR(50) NOT NULL,
city_name VARCHAR(50) NOT NULL,
no_of_people INT NOT NULL);
Pivot table data
INSERT INTO `country_wise_population` (`country_name`, `state_name`, `city_name`, `no_of_people`)
VALUES ('USA', 'NEW YORK', 'AMSTERDAM', '12');

INSERT INTO `country_wise_population` (`country_name`, `state_name`, `city_name`, `no_of_people`)
VALUES ('USA', 'NEW YORK', 'CORNING', '36');

INSERT INTO `country_wise_population` (`country_name`, `state_name`, `city_name`, `no_of_people`)
VALUES ('USA', 'WASINGTON', 'OLYMPIA', '20');

INSERT INTO `country_wise_population` (`country_name`, `state_name`, `city_name`, `no_of_people`)
VALUES ('INDIA', 'KARNATAKA', 'BANGALORE', '45');

INSERT INTO `country_wise_population` (`country_name`, `state_name`, `city_name`, `no_of_people`)
VALUES ('INDIA', 'KARNATAKA', 'MYSORE', '65');

INSERT INTO `country_wise_population` (`country_name`, `state_name`, `city_name`, `no_of_people`)
VALUES ('INDIA', 'TAMIL NADU', 'CHENNAI', '52');

INSERT INTO `country_wise_population` (`country_name`, `state_name`, `city_name`, `no_of_people`)
VALUES ('INDIA', 'KERALA', 'KOCHI', '65');
Example.1 How to create country total population Table
SELECT
   "Population",
   SUM(CASE WHEN country_name = "INDIA" THEN no_of_people ELSE 0 END) AS "INDIA",
   SUM(CASE WHEN country_name = "USA" THEN no_of_people ELSE 0 END) AS "USA"
FROM
   country_wise_population
WHERE 1;
Example.1 Output 
Example.2 How to create column and row dimension pivot table in mysql
SELECT
    state_name,
   SUM(CASE WHEN country_name = "INDIA" THEN no_of_people ELSE 0 END) AS "INDIA",
   SUM(CASE WHEN country_name = "USA" THEN no_of_people ELSE 0 END) AS "USA"
FROM
   country_wise_population
WHERE 1
GROUP BY
   country_name,state_name;
Example.2 Output 
Example. 3 Total population State wise with two row dimension state and country
SELECT
    country_name as COUNTRY,
    state_name as STATE,
    sum(no_of_people) as POPULATION
FROM
    country_wise_population
WHERE 1
    GROUP BY country_name,state_name;
Example. 3 Output 
  Copyright © 2014 Knowledge walls, All rights reserved
KnowledgeWalls
keep your tutorials and learnings with KnowledgeWalls. Don't lose your learnings hereafter. Save and revise it whenever required.
Click here for more details