SQL SERVER – PIVOT and UNPIVOT Table Examples

I previously wrote two articles about PIVOT and UNPIVOT tables. I really enjoyed writing about them as it was interesting concept. One of the Jr. DBA at my organization asked me following question.

“If we PIVOT any table and UNPIVOT that table do we get our original table?”

I really think this is good question. Answers is Yes, you can but not always. When we pivot the table we use aggregated functions. If due to use of this function if data is aggregated, it will be not possible to get original data back.

Solarwinds

Let me explain this issue demonstrating simple example.
USE AdventureWorks
GO
-- Creating Test Table
CREATE TABLE Product(Cust VARCHAR(25), Product VARCHAR(20), QTY INT)
GO
-- Inserting Data into Table
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',2)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','SODA',6)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','MILK',1)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','BEER',12)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','MILK',3)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','BEER',24)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',3)
GO
-- Selecting and checking entires in table
SELECT *
FROM Product
GO
-- Pivot Table ordered by PRODUCT
SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT
(SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
ORDER BY PRODUCT
GO
-- Pivot Table ordered by CUST
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT
(SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt
ORDER BY CUST
GO
-- Unpivot Table ordered by CUST
SELECT CUST, PRODUCT, QTY
FROM
(
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT
( SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt) p
UNPIVOT
(QTY FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)
)
AS Unpvt
GO
-- Clean up database
DROP TABLE Product
GO

ResultSet:
-- Selecting and checking entires in table
Cust Product QTY
------------------------- -------------------- -----------
KATE VEG 2
KATE SODA 6
KATE MILK 1
KATE BEER 12
FRED MILK 3
FRED BEER 24
KATE VEG 3

-- Pivot Table ordered by PRODUCT
PRODUCT FRED KATE
-------------------- ----------- -----------
BEER 24 12
MILK 3 1
SODA NULL 6
VEG NULL 5

-- Pivot Table ordered by CUST
CUST VEG SODA MILK BEER CHIPS
------------------------- ----------- ----------- ----------- ----------- -----------
FRED NULL NULL 3 24 NULL
KATE 5 6 1 12 NULL

-- Unpivot Table ordered by CUST
CUST PRODUCT QTY
------------------------- -------- -----------
FRED MILK 3
FRED BEER 24
KATE VEG 5
KATE SODA 6
KATE MILK 1
KATE BEER 12
12


You can see in above example where we are using the SUM aggregated functions. SUM adds up values based on column used in the sum function. In our example Kate and Veg has two entries. In our pivot example with order by Cust the values are summed up. Now when table goes under UNPIVOT operations it transforms the table which is already went under PIVOT operation.

Looking at the final PIVOT – UNPIVOT table is little different from the original table and it contains the sum of the two records which we have observed in the PIVOT table. You can see that result which are displayed in red fonts are summed.

This way we can get the original table back if aggregate functions was not applied on the data or data was in such form that aggregate function might have not made any difference.

Reference : Pinal Dave (https://blog.sqlauthority.com), SQL SERVER – UNPIVOT Table Example, SQL SERVER – PIVOT Table Example

Solarwinds
, , ,
Previous Post
SQL SERVER – Compound Assignment Operators – A Simple Example
Next Post
SQL SERVER – Order of Conditions in WHERE Clause

Related Posts

122 Comments. Leave new

  • Dear Sir, Please help me out as i have a column name which have space and it is giving error
    my query as below:

    SELECT *
    FROM (
    SELECT GrossAmount,ZoneName FROM PROJECTVIEW inner join ZoneMaster on PROJECTVIEW.ZoneID=ZoneMaster.ZoneID
    ) as s
    PIVOT
    (
    SUM(GrossAmount)
    FOR [ZoneName] IN (East,West,North,South,North East)
    )AS pvt

    —————North East it is giving error because of space , Please help me out

    Reply
  • sri vasanthi oruganti
    January 28, 2015 12:15 pm

    We can also use dynamic pivot if number of columns are unknown:

    DECLARE @DynamicPivot AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)

    –Get distinct values of the PIVOT Column
    SELECT @ColumnName= ISNULL(@ColumnName + ‘,’,”)
    + QUOTENAME(PRODUCT)
    FROM (SELECT DISTINCT PRODUCT FROM #Product) AS P

    –Prepare the PIVOT query using the dynamic
    SET @DynamicPivot =
    N’SELECT CUST, ‘ + @ColumnName + ‘
    FROM #Product
    PIVOT(SUM(Qty)
    FOR PRODUCT IN (‘ + @ColumnName + ‘)) AS PVTTable’
    –Execute the Dynamic Pivot
    EXEC sp_executesql @DynamicPivot

    Reply
  • Please help me with the below query, I want column_Name to be displayed in rows and and Table_Name and database are grouped as all the column for a table will be displayed in single row:

    IF OBJECT_ID(‘Tempdb.dbo.#temptbl’, ‘u’) IS NOT NULL
    DROP TABLE #temptbl

    Create Table #tempTbl
    (
    Database_Name nvarchar(100),
    Table_Name nvarchar(100),
    Column_Name nvarchar(100)
    )

    Insert Into #tempTbl
    Select DB_Name() , sys.schemas.NAME + ‘.’ + sys.tables.NAME, sys.columns.name FROM sys.tables
    INNER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id
    Inner Join sys.columns On sys.tables.object_id = sys.columns.object_id
    Go

    Select * from #tempTbl

    Reply
  • Thank you very much, it was very informative, great example

    Reply
  • I want to generate columns with date in date range and if records are found with such date in a table then assign P to thatcorresponding date column there else A…. How to do???

    Reply
  • I want to generate columns with date in date range and if records are found with such date in a table then assign P to that corresponding date column there else A…. How to do???

    Reply
  • Select Male & Female’s names from the same table Contacts using variables or stored procedure or cursors but not CTE in sql.
    I want the output as

    Male Female
    Ben Pam
    John sara
    Mike Sree
    todd Gwen

    Please help me with this query

    Thanks in advance.

    Reply
  • Jeff G, Luke
    June 16, 2017 5:09 am

    Hello Pinal,
    I`m working on Northwind DB, trying to Pivot data as shown below,

    CategoryName Sales ShippingYear
    ————— ——————— ————
    Confections 27257.51 1996
    Meat/Poultry 81338.06 1997
    Beverages 102074.31 1997
    Grains/Cereals 9219.92 1996
    Seafood 65544.18 1997
    …….

    to
    Years Beverages Condiments Confections Dairy Products Grains/Cereals Meat/Poultry Produce Seafood
    1996 46338 17754.78 27257.51 36711.37 9219.92 24617.86 12651.16 18765.97
    1997 102074.31 55277.6 80894.14 114749.78 55948.82 81338.06 53019.98 65544.18

    I tried following query
    –SELECT CategoryName, 1996, 1997
    –FROM(
    –SELECT
    –Categories.CategoryName,
    –Sum(CONVERT(money,(“Order Details”.UnitPrice*Quantity*(1-Discount)/100))*100) AS Sales,
    –YEAR(Orders.ShippedDate) AS ShippingYear
    –FROM Orders
    –INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
    –INNER JOIN Products ON [Order Details].ProductID = Products.ProductID
    –INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID
    –WHERE (((Orders.ShippedDate) Between ‘19960101’ And ‘19971231’))
    –GROUP BY Categories.CategoryID, Categories.CategoryName,YEAR(Orders.ShippedDate)
    –)p
    –PIVOT
    –(MAX(Sales) For ShippingYear IN(1996,1997)) AS pvt
    –ORDER BY Categories.CategoryID

    but this didnt work. Please help me to figure out my mistake & achieve this.

    Thanks in advance.

    Reply
  • Kumar Haripinis
    October 8, 2017 10:45 pm

    Have you ever written any code that actually works?

    Reply

Leave a Reply

Menu