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.
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
123 Comments. Leave new
Hi,
GIve me an introdiction to PIVOT and its advantages
what is the use of ‘Set Operators’,when it is use in real time?
thanks a lot for share this sql tricky
SELECT ‘Title’ AS Sort_By_Title,
[Sales Representative], [Vice President, Sales], [Sales Manager], [Inside Sales Coordinator]
FROM
(SELECT FirstName,Title FROM dbo.Employees) AS SourceTable
PIVOT
(
MAX(FirstName)
FOR Title IN
([Sales Representative], [Vice President, Sales], [Sales Manager], [Inside Sales Coordinator])
) AS PivotTable;
Incorrect syntax err. Can someone suggest?
Data Before Pivot>>
Product_Family Country_Code Day_Actual_Revenue MTD_Actual_Revenue YTD_Actual_Revenue MTD_Expected_Revenue YTD_Expected_Revenue MTD_Index_Revenue YTD_Index_Revenue Day_Actual_Units MTD_Actual_Units YTD_Actual_Units MTD_Expected_Units YTD_Expected_Units MTD_Index_Units YTD_Index_Units
HAIR US 2970.000 551709.000 1199355.000 464062.500 1039500.000 119.000 115.000 6.000 1103.000 2392.000 937.500 2100.000 118.000 114.000
ACNE US 245.000 32565.200 68877.000 56250.000 126000.000 58.000 55.000 1.000 127.000 277.000 250.000 560.000 51.000 49.000
Data After Pivot >>>
Type(Rev or Units) Label Hair_Avg Hair_Index Acne_Avg Acne_Index Total_Avg Total_Index
Revenue Day 2970 245 3215
Revenue MTD 22068.36 119 1302.61 58 23370.97 112.29
Revenue YTD 21417.05 115 1229.95 55 22647 108.81
Units Day 6 1
Units MTD 44.12 118 5.08 51
Units YTD 42.71 114 4.95 49
Can you Please Post the query for following of this
Hello there,
thank you for the explanation, it ‘been useful in my current project though I still have a concern;
Iwas wondering if it’s possible to generate dynamically the fields contained in the pivot.
A typical case would be to get sum of products based on a period, let’s say for instance between 12months as from current month;
This way, the result shown would be constantly moving from month to month and would only display the last 12 ones.
So far I only can display 12 fixed months from JAN to DEC with an additonal parameter forcing the user to change the current year.
Great Job,
Really helpfull. Thanks a lot
Hi,
I wants to show cross matrix which will contain data like
Monday Tuesday,….. Friday
Client xyz xxx
Subject testemail test
owner ABC CCC
Please let me know how it can be done?
Post some sample data with expected result
hi
how to use PIVOT in SQL Server 2000?
Look for PIVOT in SQL Server help file
dear sir i just want to convert columns to row in sql server. is it possible with pivot?
Hi all,
I am having a table containing data in the below format in sql server.
Person Day1 Day2Â Â Â Â Â Â Â Â Â Day3Â Â Â Â Â Â Â Â Day4 val
Penchal       45 58 44 98 NULL
Venkat          44 59 78 23 NULL
Vijay            48 60 66 54 NULL
Let me know the hw to make the above data as below.
Person Day val
Penchal Day1 45
Penchal Day2 58
Penchal Day3 44
Penchal Day4 98
Venkat Day1 44
venkat Day2 59
Venkat Day3 78
venkat Day4 23
.
.
.
.
.
.
Regards,
U.Penchal Reddy.
Search for UNPIVOT in this site
I had a hard time finding useful articles for a beginner like me, but this was an excellent article! Thanks so much
thanx madhivanan……it is working..,..
regards
penchal reddy.u
Hi All,
I created a table as shown below.By using ssis package DeptID 10 data should be transfered into seperate table ,DeptID 20 data should be transfered into seperate table…..but the condition is that when i entered DeptID 50,60……in the original table for each and every DeptID seperate tables should be created in the Database when i run the package.can anyone give clue for this pls……
DeptID DeptName Location
10 computers chennai
10 computers Hyd
20 electronics Delhi
20 electronics Hyd
20 electronics mumbai
30 commerce CPT
30 commerce Gunture
30 commerce NRT
30 commerce VNK
40 maths BAD
Thanks,
U.Penchal Reddy.
Hi, i have this code, how it changes to sql server 2000 ??
TRANSFORM Count(dbo_CLIENTES.FECHA_CREACION) AS CuentaDeFECHA_CREACION
SELECT dbo_CLIENTES.CLI_EMPRESA AS EMPRESA
FROM dbo_C_CALZADO INNER JOIN dbo_CLIENTES ON dbo_C_CALZADO.ID_CLIENTES = dbo_CLIENTES.ID_CLIENTES
WHERE (((dbo_CLIENTES.EXTRANJERO)=”TRUE”))
GROUP BY dbo_CLIENTES.CLI_EMPRESA
ORDER BY dbo_CLIENTES.CLI_EMPRESA
PIVOT Year(DBO_CLIENTES.FECHA_CREACION);
Thanks a lot !!
Search for PIVOT in this site
in your example, while doing unpivot, do we need to add pivot then unpivot………
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
——————————————-
here, you added pivot and unpivot…….
is there nay need of pivot
Hi, i am currently working on a project where I have a column called ‘Month’ which has months from Jan to Dec and correspondingly have a column called bill amount. I have to to display the bill amount for every month individually and then the sum of those . I am able to get the sum using PIVOT. But I am not able to display those values. Is there any way that I can display the moth and then the sum? Thank you.
hi All,
I have a requirement where the table values look like below
Parent ID is also a document ID. For each parent ID there may be one or more child document associated.
Document ID Document# doc Name Doc start date end date parent ID
101 0001 Aaa 1/1/90 1/2/90 334
102 0003 Sss 3/3/99 3/4/99 334
222 4566 Ano 5/5/05 6/6/06 null
334 9879 Parent 1/1/89 1/1/89 null
Now in my report, I need data as below
Document ID1 Doc start date1 end date1 DocumentID2 Doc start date2 end date2
101 1/1/90 1/2/90 102 3/3/99 3/4/99
please help
thanks
preethi
Good site..
My query is…
i have 3 columns
item, remarks, remarksby, date
a, ‘good’, ‘sonu’ , day1
a, ‘bad’, monu’ , day2
d, ‘too good’, ‘raju’ , day1
d, ‘ok’, ‘golu’ , day2
i have to show like this…
item , remarks1, remakrsby1, remarks2, remarksby2, remakrs3,remarksby3
a , good, sonu, bad, monu
d, too good , raju, ok , golu
please let me know how to do this … ???
All thanks in advance
declare @remarks table (item varchar, remarks varchar(20), remarksby varchar(20) ,dat varchar(20))
insert into @remarks
select ‘a’, ‘good’, ‘sonu’ , ‘day1’ union all
select ‘a’, ‘bad’, ‘monu’ , ‘day2’ union all
select ‘d’, ‘too good’, ‘raju’ , ‘day1’ union all
select ‘d’, ‘ok’, ‘golu’, ‘day2’
–select * from @remarks
SELECT
item,
max(case when id=1 then remarks
end) as remarks1 ,
max(case when id=2 then remarks
end) as remarks2,
max(case when id=1 then remarksby
end) as remarksby2,
max(case when id=2 then remarksby
end) as remarksby2
FROM (
SELECT
row_number()over (partition by item order by item)id,
item,
remarks,
remarksby
FROM @remarks
) up
group by
item