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 (http://blog.SQLAuthority.com), SQL SERVER – UNPIVOT Table Example, SQL SERVER – PIVOT Table Example
pinaldave
Very much useful article .
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
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?
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
[...] SQL SERVER – PIVOT and UNPIVOT Table Examples SQL SERVER – PIVOT Table Example SQL SERVER – UNPIVOT Table Example [...]
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?
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
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 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
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
You have a table called Product with a Field called Product.
Hi,
I have a query regarding the cross tab query. I am generating a report in excel format e.g. survey report. curently I have create a stored procedure for that, everything is working fine for those 5 option for the survey question (Agree,disagree,neutral etc..) I have specified in the stored procedure,Now if admin enter one more option for the survey question answer, Now I don’t want admin to go to the procedure code and from dotnet code to chnage everything.
Can We do this dynamically sso that when a admin add a new option for any wuestion then it should automaticalyyy display that option.
Can Anyone suggest me how to do that I here by pasting the code for my procedure..
SELECT P1.*, (P1.Option1 + P1.Option2 + P1.Option3 + P1.Option4 + P1.Option5) AS TotalVotes
FROM (SELECT
Surveys.Question,
–SurveyResults.SurveyOptionID,
Option1 = COUNT(CASE WHEN SurveyOptions.OptionName=’STRONGLY AGREE’ THEN SurveyResults.SurveyOptionID END),
Option2 = COUNT(CASE WHEN SurveyOptions.OptionName=’AGREE’ THEN SurveyResults.SurveyOptionID END),
Option3 = COUNT(CASE WHEN SurveyOptions.OptionName=’NEUTRAL’ THEN SurveyResults.SurveyOptionID END),
Option4 = COUNT(CASE WHEN SurveyOptions.OptionName=’DISAGREE’ THEN SurveyResults.SurveyOptionID END),
Option5 = COUNT(CASE WHEN SurveyOptions.OptionName=’STRONGLY DISAGREE’ THEN SurveyResults.SurveyOptionID END)
/*Surveys.OptionType,*/
–SurveyResults.UserID
–SurveyOptions.OptionName
–SurveyOptions.IsCorrect
FROM
SurveyResults INNER JOIN
SurveyOptions ON SurveyResults.SurveyOptionID = SurveyOptions.SurveyOptionID INNER JOIN
Surveys ON SurveyOptions.SurveyID = Surveys.SurveyID
WHERE
(Surveys.ModuleID = @ModuleID)
and
(SurveyResults.UserID 0)
and
(Convert(varchar(10),SurveyResults.SurveyDate,101) >=@FromDate
and Convert(varchar(10),SurveyResults.SurveyDate,101) <=@ToDate )
GROUP BY Surveys.Question,Surveys.SurveyID ) AS P1
@sansugoi
Since you did not mention, any input, I assumed input as STRONGLY AGREE,AGREE,NEUTRAL,DISAGREE. You can change it how ever you want.
Please follow the order of the input, comma seperated with no spaces. otherwise procedure might not execute.
So this is the procedure, check this out, and let us know if you have questions. at the end of procedures, I have given, samples to execute, take a look of it.
– Start of procedure.
Create Procedure USP_Generate_DynamicSQL (@OptionCompleteName varchar(8000) )
AS
SET NOCOUNT ON
– First Lets seperate out comma seperated values and store in a table variable.
Declare @table table (Ident int Identity , OptionName varchar(40))
Declare @Length int
Declare @Store_Name varchar (40)
– Start of Storing comma seperated values into a table variable
Set @length = 1
While @length != 0
begin
Set @Store_Name = NULL
Set @length = 1
Set @Store_Name = substring ( @OptionCompleteName , @length, case when (charindex (‘,’ , @OptionCompleteName)-1)= -1 then len(@OptionCompleteName) else (charindex (‘,’ , @OptionCompleteName)-1) End )
insert into @table values (@Store_Name )
set @length = (charindex (‘,’ , @OptionCompleteName)+1)
If @length != 1
begin
Set @OptionCompleteName = substring ( @OptionCompleteName, @length, datalength (@OptionCompleteName) + 1 – @length)
set @length = (charindex (‘,’ , @OptionCompleteName)+1)
End
Else
Begin
Set @OptionCompleteName = substring ( @OptionCompleteName , 1, datalength (@OptionCompleteName))
Set @length = 0
End
End
– End of Storing comma seperated values into a table variable
– Lets build the first short select statement dynamically.
Declare @Select_Statement1 varchar(8000)
Set @Select_Statement1 = ”
select @Select_Statement1 = @Select_Statement1 +case when @Select_Statement1 = ” then ” else ‘+’ End +’P1.Option’+convert (varchar, Ident)
from @table
– print @Select_Statement1
– Now lets build our final select statement dynamically.
Declare @Select_Statement2 varchar(8000)
Set @Select_Statement2 = ‘
SELECT P1.*, (‘+@Select_Statement1+’) AS TotalVotes
FROM (
SELECT
Surveys.Question’
Declare @count int
Set @count = 1
Declare @Ident int
Declare @OptionName varchar(40)
While @Count =@FromDate
and Convert(varchar(10),SurveyResults.SurveyDate,101) <=@ToDate )
GROUP BY Surveys.Question,Surveys.SurveyID ) AS P1'
print @Select_Statement2
SET NOCOUNT OFF
GO
– Sample to Execute
Exec USP_Generate_DynamicSQL 'STRONGLY AGREE,AGREE,NEUTRAL,DISAGREE,STRONGLY'
Exec USP_Generate_DynamicSQL 'STRONGLY AGREE,AGREE,NEUTRAL,DISAGREE'
~ IM
Hi Guys,
I have the following 4 records which reflect the price of a product in 4 different stores.
Rec # Commodity_Name Product_Description Product_ID Price
1 AIRFRESHNER 200ML MORNING MIST 31 2.99
2 AIRFRESHNER 200ML MORNING MIST 42 1.99
3 AIRFRESHNER 200ML MORNING MIST 83 3.99
4 AIRFRESHNER 200ML MORNING MIST 94 4.99
Is it possible to display the above fields for only record 1 and only the price fields for records 2, 3 and 4 on the same line using the Pivot Statement?
I’m using SQL Server 2008 and just wondering if there much of an overhead using the Pivot Statement??
Thanks.
Hi All,
I want to combine 3 columns into one column. It must group by Col_1 and then gourp by Col_2. I am just wondering if there is easier way to do it. Thanks.
Create table tbl(Col_1 varchar(50),Col_2 varchar(50), Col_3 varchar(50))
Insert into tbl values(‘A’,'B’,'C’)
Insert into tbl values(‘A’,'B’,'D’)
Insert into tbl values(‘A’,'B’,'E’)
Insert into tbl values(‘A’,'B1′,’F')
Insert into tbl values(‘A’,'B1′,’G')
Insert into tbl values(‘A’,'B1′,’H')
Insert into tbl values(‘A’,'B2′,’I')
Insert into tbl values(‘A’,'B2′,’J')
Insert into tbl values(‘A’,'B2′,’L')
Insert into tbl values(‘A1′,’B',’M')
Insert into tbl values(‘A1′,’B1′,’N')
One_Col
A
B
C
D
E
B1
F
G
H
B2
I
G
L
A1
B
M
A1
B1
N
Drop table tbl;