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
Very much useful article .
I have a requirement in which after Pivot, I don’t want the aggregated value, instead I need ‘Y’ and for Null values it should be ‘N’. How can I achieve this? Please help me.
Your site is too good. There are somany things to learn….
Please suggest How to fully convert 2×3 dimension to 3×2 .
that is fully reverse rows into cloumns and columns into rows.
fine mr!…
can we write a select statement in IN OF PIVOT
PIVOT
( SUM(QTY)
FOR PRODUCT
IN (here i need to write select statement can we do it)
) AS pvt)
waiting 4 replay
did you got the answer for this?
Hi – anyone have an answer to this one? very NB!
Did anyone reply? I am facing this same issue.
I have tried many times when I using sql select statement, but it always failed.
If any knows, please share it.
See here for a solution to dynamically determine the column headers via a query (this technique requires the creation of a stored procedure that constructs the SQL dynamically and then runs it):
Hi,
Great article!
I have a question, is there any way to unpivot a table with different data types without having to cast every single column into a common data type?
In your example and most examples I have seen writers use integers but if I need to unpivot a table with several columns with different data types (integers, nvarchars of different sizes, etc.) I have to cast every column into nvarchar. Is there a quick way to do this? Maybe part of the unpivot syntax?
NO Man
I am having a problem with the Pivot…is there something wrong with my syntax…it just returns the column names as values…
Select BidId,’Cancelled’,
‘In Progress’,
‘Loss’,
‘New’,
‘No Bid’,
‘Referred Out’
FROM (
Select bi.BidId, dbo.Status.Name, dbo.StatusComments.CommentDate
From Bids bi INNER JOIN
dbo.BidFormOwning ON bi.BidId = dbo.BidFormOwning.BidId INNER JOIN
dbo.BidRevision ON dbo.BidFormOwning.BidFormOwningId = dbo.BidRevision.BidFormOwningId INNER JOIN
dbo.StatusComments ON dbo.BidRevision.BidRevisionId = dbo.StatusComments.RevisionId INNER JOIN
dbo.SupportTeams ON dbo.BidFormOwning.SupportTeamId = dbo.SupportTeams.SupportTeamId INNER JOIN
dbo.Status ON dbo.StatusComments.StatusId = dbo.Status.StatusId
where dbo.BidFormOwning.SupportTeamId=2
and dbo.Status.Name in (‘Cancelled’,
‘In Progress’,
‘Loss’,
‘New’,
‘No Bid’,
‘Referred Out’)) as a
Pivot
(
Count(CommentDate)
FOR Name In ([Cancelled],
[In Progress],
[Loss],
[New],
[No Bid],
[Referred Out])) as b
order by BidId
Instead the quotes use the brackets, like:
Select BidId,[Cancelled],
[In Progress],
[Loss],
[New],
[No Bid],
[Referred Out]
Hi Dave,
upto now what i have seen the blog’s,this is the goodone.
keep it up.
it’s a very useful one.
Cheers,
Ravindra
Hi Dave,
It is really interesting concept. I would like to learn more about this concept. Where can I get more information about PIVOT and UNPIVOT?
Have a loot at SQL Server help file
It has examples with explanation
Hi,
I have two Columns State and City. When I select them from table, it displays like
STATE CITY
Connecticut Hartford
Connecticut Stamford
Connecticut New Haven
Connecticut Bridgeport
But I need them as
STATE CITY1 CITY2 CITY3 CITY4
Connecticut Hartford Stamford New Haven Bridgeport
So that I can use them in my SP. There are many cities for some states.
Can you please help me out by using Pivot?
hi
iam new to this website.i want to learn sqlserver.2005 or 2008 which one is better.can any one give an idea how to start and where to start.plz help me
thanks in advance
Hi ,
Very useful concept. It helped me a lot !!!!
hi..
code sql nya kerennnnnnnnnnnnnnn banget dah
sangat bermamfaat..
salam dari Indonesia….
I have a table with two columns, labeled Year and Loss. In the Year
field, I have the numbers 1 to 10,000, each which can or cannot
repeat. In the Loss column, i have numbers corresponding to the
Years…for example:
Year, Loss
1, 568
1, 621
1, 358
1, 7888
2, 2689
2, 6563
2, 15
3, 983
3, 146
3, 258
3, 852
4, 96
5, 87
5, 32
So, you see, Year 1 can have four losses, Year 2 can have three
losses, etc.
Now, here’s where I need help as I’m not sure what to do given that I
am a beginner at SQl and learning: I want to have just one row for
each Year. So, for Year 1, I would like to have four extra columns,
and for Year 2, I would like to have three columns created to hold
loss numbers so they display in a row – please see below for example:
Year, Loss1, Loss2, Loss3, Loss4, Loss5, etc.
1, 568, 621, 358, 7888
2, 2689, 6563, 15
3, 983, 146, 258, 852
4, 96
5, 87, 32
I hope that the examples helped in clarifying what kind of Query I
need.
plz send me solution
Dear Atul,
Have u got any solution for the Problem.
please share because i am also facing the same.
Please send me t
hi.. i have a table tblData containing columns fanSerialNo,d1,d2,d3,d4..
this is how the table return values
fanSerialNo d1 d2 d3 d4
101 10 11 12 13
but i want the resultset like
fanserialNo days qty
101 d1 10
101 d2 11
101 d3 12
101 d4 13
Can anyone help me??
Thanx
how to use pivot in sql server 2000,
if i am select varchar data type data in pivot
article hold great content regarding pivot unpivot but the question always arises in my mind is that what is the exact defination of a Pivot and Unpivot table…..
Hi
I need to do the same PIVOT functionality in Sql 2000, can some one help me
Eg:
Item cp amnt
A R1 10
A R2 10
B R1 4
B R2 5
I need this to be formatted like
Item R1 R2
A 10 10
B 4 5
Thanks
Hi
I know It’s too late to answer you but this ans will be useful for future help to others
create table _temp (item nvarchar(15),cp nvarchar(10),amnt float)
insert into _temp values(‘A’,’R1′,10)
insert into _temp values(‘A’,’R2′,10)
insert into _temp values(‘B’,’R1′,4)
insert into _temp values(‘B’,’R2′,5)
select * from _temp
select item,[R1],[R2]
from ( select cp,item,amnt from _temp )as s1
pivot
(sum(amnt) for cp in ([R1],[R2])) as pivottable
Hi,
i have table like below ,
Product Name Price Date
Apple 1.5 5/5/2009
Apple 3 5/6/2009
Apple 3.5 5/7/2009
Apple 2.5 5/8/2009
Apple 5.5 5/9/2009
Orange 10.5 5/5/2009
Orange 12.5 5/6/2009
Orange 7.5 5/7/2009
Orange 4.5 5/8/2009
Orange 5.5 5/9/2009
I need output like below
Product Name 5/5/2009 5/6/2009 5/7/2009 5/8/2009 5/9/2009
Apple 1.5 3 3.5 2.5 5.5
Orange 10.5 12.5 7.5 4.5 5.5
also date increases column also need to increase,
Pls help me
Vickees
You need Dyanmic SQL
Refer this
Hi guys,
i have table like below ,
Product Name Price Date
Apple 1.5 5/5/2009
Apple 3 5/6/2009
Apple 3.5 5/7/2009
Apple 2.5 5/8/2009
Apple 5.5 5/9/2009
Orange 10.5 5/5/2009
Orange 12.5 5/6/2009
Orange 7.5 5/7/2009
Orange 4.5 5/8/2009
Orange 5.5 5/9/2009
I need output like below
Product Name 5/5/2009 5/6/2009 5/7/2009 5/8/2009 5/9/2009
Apple 1.5 3 3.5 2.5 5.5
Orange 10.5 12.5 7.5 4.5 5.5
also date increases column also need to increase,
Pls help me
Vickees
can we write a select statement in IN OF PIVOT
PIVOT
( SUM(QTY)
FOR PRODUCT
IN (here i need to write select statement can we do it)
) AS pvt)
waiting 4 replay
No it is not possible
Only possible method is concatenate all the values that are returned from select statement and use that inside IN
It should be a dynamic SQL which you can get some ideas from here
Build a dynamic query instead, this will work for you :)