Knowledge Walls
John Peter
Pune, Maharashtra, India
Mysql Indeep
Blob in select query result How to get total milliseconds, seconds, minutes and hour by Date in Mysql How to convert upper and lower in mysql blob, binary and varbinary fields data? How to get Current TimeZone Date and UTC or GMT date time in Mysql How to Create dynamic pivot query in mysql with Example Number and Exponential number rounding issue in Mysql with Example Remove decimal places without rounding in mysql using truncate with Example
Smart Tricks
MATCH.. AGAINST.. FULL TEXT Search and Result Ordered by more relevance Case sensitive string comparison in mysql with example How to copy table with constraints in mysql? SELECT Query Serial no creation Comparing multiple columns with single value IN() with multiple column values
Procedure
Procedure Recursion Multiple cursors in mysql stored procedure with example How to create stored procedure in mysql with routine comment example How to find factorial with mysql recursive stored procedure with example
INFORMATION_SCHEMA Db
`INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` `INFORMATION_SCHEMA`.`ROUTINES` `INFORMATION_SCHEMA`.`COLUMNS` `INFORMATION_SCHEMA`.`TABLES`
Cool Topics
NULL-safe equal and not equal operators in mysql How to ignore constraints while insert,update or delete records in Mysql? How to drop all constraints in Mysql? How to manage users in Mysql? Uppercase first letter(ucfirst) in mysql with example Automatic insert and update timestamp on Mysql table's IFNULL method with Example
Mysqldump
Dumping Database in MYSQL Mysqldump routines without definer in Linux Two ways to Change password of root user in Mysql Dumping Triggers / Skip Triggers in mysqldump Dump in mysql with ignore only few table data Dumping Table in MYSQL
Methods
COMPRESS() and DECOMPRESS() Method in Mysql with Example HEX() and UNHEX() method in MySQL with Example COALESCE() method in Mysql with Example ASCII() and CHAR() Method in Mysql with Example INET_ATON() and INET_NTOA() Method in Mysql with Example AES_ENCRYPT() and AES_DECRYPT() Method in Mysql with Example GREATEST() and LEAST() Method in Mysql with Example
Methods [cont'd]
DES_ENCRYPT() and DES_DECRYPT() Method in Mysql with Example
My.ini Configuration Settings
How to configure current or default time zone as GMT in Mysql using my.ini file
Difference between
DETERMINISTIC vs NON-DETERMINISTIC
How to Create dynamic pivot query in mysql with Example
28539 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