TechSapphire Saturday, 2018-04-21, 11:59 PM
Site menu
Login form
News
Play Games
  • Deep Freeze
  • Ice Slide
  • Gyroball
  • Fat Fish
  • Bush Royal Rampage
  • 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
    Categories
    Programming [27]
    Tips for programming
    Security [2]
    Security Tips
    Google [1]
    Use google faster then ever you use
    Project [14]
    HTML [2]
    Electronics [0]
    Data Structure [0]
    Database [16]
    SQL SERVER
    SSRS [1]
    Sql Server Reporting Services
    Copyright MyCorp © 2018