Introduction to MySQL Cross Tabb Query

There is nothing like “PIVOT” of SQL Sever 2005 and later versions in MySQL Database to accomplish Cross Tabb Quires. But it can be done using IF here. However, you can not execute Cross Tabb Query for unknown values in MySQL Server which is somehow possible in SQL Server. You can have a look at my posting on SQL Server Cross Tabb Quires. Let’s jump into coding to explore MySQL Cross Tabb query.


CREATE TABLE `t_sale_summary` (
`SaleDate` DATE NOT NULL DEFAULT '0000-00-00',
`CategoryName` VARCHAR(50) NOT NULL DEFAULT '',
`SaleAmount` DECIMAL(50,0) NULL DEFAULT NULL,
PRIMARY KEY (`SaleDate`, `CategoryName`)
)
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

Insert some demo data in t_sale_summary.


INSERT INTO 
`t_sale_summary` (`SaleDate`, `CategoryName`, `SaleAmount`) 
VALUES ('2010-08-06', 'DRINKS', 10000), 
('2010-08-06', 'FOOD', 20000), 
('2010-08-07', 'DRINKS', 12000), 
('2010-08-07', 'FOOD', 20000), 
('2010-08-08', 'DRINKS', 30000), 
('2010-08-08', 'FOOD', 10000);


SELECT * FROM t_sale_summary;


This is your original Table Data.

To retrieve this type of result set from existing data, you have to make Cross Tabb Query using IF.


SELECT Saledate,
SUM(IF (CategoryName = 'DRINKS',SaleAmount,0) ) AS DRINKS,
SUM(IF (CategoryName = 'FOOD',SaleAmount,0) ) AS FOOD
FROM t_sale_summary
GROUP BY Saledate
ORDER BY SaleDate DESC;

Thanks
A Rahim Khan

Advertisements
  1. good post

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: