Generating matrix using SQL queries using CUBE, PIVOT and ROLLUP

Generating matrix in SQL is bit tricky, in above lecture you will learn to generate matrix using CUBE and PIVOT, also difference between CUBE and ROLLUP.

Script used in above lecture:

--Matrix using SQL query, using CUBE, PIVOT and ROLLUP
--Creating sample table having Year,Term,Client and Sales done.
CREATE TABLE SALES ([Year] VARCHAR(6), [Term] VARCHAR(100), [Client] VARCHAR(100), Sales MONEY);

INSERT INTO SALES
VALUES ('2014', 'Quarter1', 'Wallmart', 18000), ('2014', 'Quarter2', 'Wallmart', 81000), ('2014', 'Quarter3', 'Wallmart', 72110), ('2014', 'Quarter4', 'Wallmart', 91000), ('2014', 'Quarter1', 'GE', 31000), ('2014', 'Quarter2', 'GE', 81200), ('2014', 'Quarter3', 'GE', 95000), ('2014', 'Quarter4', 'GE', 17721), ('2014', 'Quarter1', 'Pepsi', 90002), ('2014', 'Quarter2', 'Pepsi', 53001), ('2014', 'Quarter3', 'Pepsi', 80210), ('2014', 'Quarter4', 'Pepsi', 90203), ('2014', 'Quarter1', 'Raymond', 109220), ('2014', 'Quarter2', 'Raymond', 89000), ('2014', 'Quarter3', 'Raymond', 100000), ('2014', 'Quarter4', 'Raymond', 910203), ('2015', 'Quarter1', 'Wallmart', 18000), ('2015', 'Quarter2', 'Wallmart', 81000), ('2015', 'Quarter3', 'Wallmart', 72110), ('2015', 'Quarter4', 'Wallmart', 91000), ('2015', 'Quarter1', 'GE', 31000), ('2015', 'Quarter2', 'GE', 81200), ('2015', 'Quarter3', 'GE', 95000), ('2015', 'Quarter4', 'GE', 17721), ('2015', 'Quarter1', 'Pepsi', 90002), ('2015', 'Quarter2', 'Pepsi', 53001
 ), ('2015', 'Quarter3', 'Pepsi', 80210), ('2015', 'Quarter4', 'Pepsi', 90203), ('2015', 'Quarter1', 'Raymond', 109220), ('2015', 'Quarter2', 'Raymond', 89000), ('2015', 'Quarter3', 'Raymond', 100000), ('2015', 'Quarter4', 'Raymond', 910203);
GO

--Querying view to see data returned
SELECT [Year], [Term], [Client], Sales
FROM SALES
GO

--Creating view for running queries on basic set only
CREATE VIEW ThreeColumnView
AS
SELECT [Term], [Client], Sales
FROM SALES
WHERE [YEAR] = 2014
GO

--Querying view to see data returned
SELECT [Term], [Client], Sales
FROM ThreeColumnView

--Querying view to see total sale of each client in each term
SELECT [Term], [Client], SUM(Sales) [Sales]
FROM ThreeColumnView
GROUP BY [Term], [Client]
ORDER BY [Term], [Client]

--Running cube on data returned by view
SELECT [Term], [Client], SUM(Sales)
FROM ThreeColumnView
GROUP BY [Term], [Client]
WITH CUBE

--Removing NULL by replacing it with XTotal
SELECT ISNULL([Term], 'xTotal') AS [Term], ISNULL([Client], 'XTotal') AS [Client], SUM(Sales)
FROM ThreeColumnView
GROUP BY [Term], [Client]
WITH CUBE

--Creating Matrix
SELECT *
FROM (
 SELECT ISNULL([Term], 'xTotal') AS [Term], ISNULL([Client], 'XTotal') AS [Client], SUM(Sales) AS [Sales]
 FROM ThreeColumnView
 GROUP BY [Term], [Client]
 WITH CUBE
 ) x
PIVOT(SUM(SALES) FOR [Term] IN (
 Quarter1,
 Quarter2,
 Quarter3,
 Quarter4,
 xTotal
 )) p

--Quarter1,Quarter2,Quarter3,Quarter4,xTotal
--Query on full dataset
--Viewing complete data from Sales table
SELECT [YEAR], [Term], [Client], Sales
FROM SALES

--Grouping and checking summary for each client
SELECT [YEAR], [Term], [Client], SUM(Sales) [Sales]
FROM SALES
GROUP BY [YEAR], [Term], [Client]

--CUBE output of complete dataset
SELECT [YEAR], [Term], [Client], SUM(Sales)
FROM SALES
GROUP BY [YEAR], [Term], [Client]
WITH CUBE

--Getting rid of all NULL by replacing it with xTotal
SELECT ISNULL([YEAR], 'xTotal') [Year], ISNULL([Term], 'xTotal') [Term], isnull([Client], 'xTotal') [Client], SUM(Sales) [Sales]
FROM SALES
GROUP BY [YEAR], [Term], [Client]
WITH CUBE

--Matrix Query With CUBE
SELECT *
FROM (
 SELECT ISNULL([YEAR], 'xTotal') [Year], ISNULL([Term], 'xTotal') [Term], isnull([Client], 'xTotal') [Client], SUM(Sales) [Sales]
 FROM SALES
 GROUP BY [YEAR], [Term], [Client]
 WITH CUBE
 HAVING (
 [YEAR] IS NOT NULL
 AND [Client] IS NOT NULL
 )
 OR (
 [YEAR] IS NULL
 AND [Client] IS NULL
 )
 OR (
 [YEAR] IS NOT NULL
 AND [Client] IS NULL
 )
 ) x
PIVOT(SUM(Sales) FOR Term IN (
 Quarter1,
 Quarter2,
 Quarter3,
 Quarter4,
 xTotal
 )) p
ORDER BY [YEAR]

--Checking ROLLUP output of complete dataset
SELECT [YEAR], [Term], [Client], SUM(Sales)
FROM SALES
GROUP BY [YEAR], [Term], [Client]
WITH ROLLUP

--Getting rid of all NULL by replacing it with XTotal
SELECT ISNULL([YEAR], 'xTotal') [Year], ISNULL([Term], 'xTotal') [Term], isnull([Client], 'xTotal') [Client], SUM(Sales) [Sales]
FROM SALES
GROUP BY [YEAR], [Term], [Client]
WITH ROLLUP

--Matrix Query With ROLLUP
SELECT *
FROM (
 SELECT ISNULL([YEAR], 'xTotal') [Year], ISNULL([Term], 'xTotal') [Term], isnull([Client], 'xTotal') [Client], SUM(Sales) [Sales]
 FROM SALES
 GROUP BY [YEAR], [Term], [Client]
 WITH ROLLUP
 ) x
PIVOT(SUM(Sales) FOR Term IN (
 Quarter1,
 Quarter2,
 Quarter3,
 Quarter4,
 xTotal
 )) p
ORDER BY p.Year

DROP VIEW ThreeColumnView

DROP TABLE SALES

 --Contact Information
 --Name: Yogesh Mehla
 --Email: yogesh.mehla@gmail.com
 --Website: www.techsapphire.in
 --Phone: +91-9023262520
 --Skype: Kingconspiracy