Database Creation
CREATE DATABASE SALESDB;
USE SALESDB;
Table Creation
CREATE TABLE sales (product VARCHAR(20), region VARCHAR(20), sale INT);
INSERT INTO sales (product, region, sale)
VALUES
('A', 'LHR', 100),
('A', 'KHI', 150),
('B', 'LHR', 50),
('C', 'LHR', 60),
('B', 'KHI', 30),
('C', 'KHI', 10),
('A', 'LHR', 20),
('A', 'FSD', 30),
('B', 'FSD', 40),
('C', 'FSD', 12)
Dynamic PL/SQL Cross-Tab Query
First Case
Statement:
DECLARE
query varchar(32767) := 'select case when Region IS NULL then ''Total'' else Region end as Region';
BEGIN
For i in (select distinct product from sales order by 1)
LOOP
query := query ||' ,SUM(case when product = '''||i.product||''' then Sale else 0 end) as '|| i.product;
END LOOP;
query := query ||',sum(sale) as "Total" from sales group by rollup (region)';
dbms_output.put_line(query);
end;
Output
SELECT case when Region IS NULL then 'Total' else Region end as Region
,SUM(case when product = 'A' then Sale else 0 end) as A
,SUM(case when product = 'B' then Sale else 0 end) as B
,SUM(case when product = 'C' then Sale else 0 end) as C
,sum(sale) as "Total"
FROM sales
GROUP BY ROLLUP (region)
The following query when executed gives:
Second Case
Statement:
DECLARE
query varchar(32767) := 'select case when Product IS NULL then ''Total'' else Product end as Product';
BEGIN
For i in (select distinct Region from sales order by 1)
LOOP
query := query ||' ,SUM(case when Region = '''||i.Region||''' then Sale else 0 end) as '|| i.Region;
END LOOP;
query := query ||',sum(sale) as "Total" from sales group by rollup (Product)';
dbms_output.put_line(query);
end;
Output
SELECT case when Product IS NULL then 'Total' else Product end as Product
,SUM(case when Region = 'LHR' then Sale else 0 end) as LHR
,SUM(case when Region = 'KHI' then Sale else 0 end) as KHI
,SUM(case when Region = 'FSD' then Sale else 0 end) as FSD
,sum(sale) as "Total"
FROM sales
GROUP BY ROLLUP (Product)
The following query when executed gives:
Alternative MYSQL Statements
Using Case Statement:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('SUM(case when product = ''',Product,''' then sale end)AS ''',product, '''')) INTO @sql
FROM sales;
SET @sql = CONCAT('SELECT case when region IS NULL then ''Total'' else region end as region, ', @sql,
',sum(sale) as ''Total'' FROM sales GROUP BY sales.region desc with rollup');
PREPARE stmt FROM @sql;
EXECUTE stmt;
Using IF Statement:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('SUM(if(product = ''',Product,''', sale,NULL))AS ''',product, '''')) INTO @sql
FROM sales;
SET @sql = CONCAT('SELECT ifnull(region,''Total'') as ''Region'', ', @sql, ',sum(sale) as ''Total'' FROM sales
GROUP BY sales.region desc with rollup');
PREPARE stmt FROM @sql;
EXECUTE stmt;
Output
Second Case
Using Case Statement:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('SUM(case when Region = ''',Region,''' then sale end)AS ''',Region, '''')) INTO @sql
FROM sales;
SET @sql = CONCAT('SELECT case when product IS NULL then ''Total'' else product end as product, ',
@sql, ',sum(sale) as ''Total'' FROM sales GROUP BY sales.product with rollup');
PREPARE stmt FROM @sql;
EXECUTE stmt;
Using IF Statement:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('SUM(if(region = ''',region,''', sale,NULL))AS ''',region, '''')) INTO @sql
FROM sales;
SET @sql = CONCAT('SELECT ifnull(product,''Total'') as ''product'', ', @sql, ',sum(sale) as ''Total'' FROM
sales GROUP BY sales.product with rollup');
PREPARE stmt FROM @sql;
EXECUTE stmt;
Output
