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:
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
SELECT [Year], [Term], [Client], Sales
FROM SALES
GO
CREATE VIEW ThreeColumnView
AS
SELECT [Term], [Client], Sales
FROM SALES
WHERE [YEAR] = 2014
GO
SELECT [Term], [Client], Sales
FROM ThreeColumnView
SELECT [Term], [Client], SUM(Sales) [Sales]
FROM ThreeColumnView
GROUP BY [Term], [Client]
ORDER BY [Term], [Client]
SELECT [Term], [Client], SUM(Sales)
FROM ThreeColumnView
GROUP BY [Term], [Client]
WITH CUBE
SELECT ISNULL([Term], 'xTotal') AS [Term], ISNULL([Client], 'XTotal') AS [Client], SUM(Sales)
FROM ThreeColumnView
GROUP BY [Term], [Client]
WITH CUBE
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
SELECT [YEAR], [Term], [Client], Sales
FROM SALES
SELECT [YEAR], [Term], [Client], SUM(Sales) [Sales]
FROM SALES
GROUP BY [YEAR], [Term], [Client]
SELECT [YEAR], [Term], [Client], SUM(Sales)
FROM SALES
GROUP BY [YEAR], [Term], [Client]
WITH CUBE
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
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]
SELECT [YEAR], [Term], [Client], SUM(Sales)
FROM SALES
GROUP BY [YEAR], [Term], [Client]
WITH ROLLUP
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
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
|