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
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;
Try this
select col_1 from tbl
union
select col_2 from tbl
union
select col_3 from tbl
I have calendar table which has statdate, enddate, QTR ect. data. We are pulling Orders by QTR. I like to create a a cross-tab report which looks like this. If it possible to do this. Please help…..
FY10 FY09 FY08 FY07 FY09
Q1 5 15 17 18 40
Q2 0 2 22 21 10
Q3 55 11 10 8 5
Q4 5 1 2 0
Total
Hi,
I am using SQL Server 2000 in one of my project and querying one issue.
i.e.
Is there any alternate solution of using PIVOT/UNPIVOT in SQL Server 2000??
Regards
Raj Sukhija
There is alternate with case expression. Refer this post for more informations
Hi,
I have data in table in following format:-
StatsName StatsValue
Sample(S) S(1)
BPI-Sys 48
BPD-DIA 17
HT 153
WT 68
Age 28
BMI 77
Resting Pulse 88
Total Colestral 99
LDL 18
HDL 17
Sample(S) S(2)
BPI-Sys 8
BPD-DIA 7
HT 8
WT 9
Age 8
BMI 7
Resting Pulse 8
Total Colestral 9
LDL 8
HDL 7
And I want result in following format using Pivot:-
Sample(S) BPI-Sys BPD-DIA HT WT Age BMI Resting Pulse Total Colestral LDL HDL
S(1) 48 17 153 68 28 77 88 99 18 17
S(2) 8 7 8 9 8 7 8 9 8 7
How to do it please help me.
Hi,
I need a Solution for the Following
I have two Tables with following Structure
——————————
Table : Allowance
————————————–
Id Name CountryID
————————————–
1 Allowance 1 1
1 Allowance 1 2
2 Allowance 2 3
2 Allowance 2 2
……………………………………..
………………………………………
—————————–
Table : Country
————————–
ID CountryName
————————-
1 India
2 United States
3 Japan
………………………..
……………………….
——————–
Using following PIVOT query I got the out put
———————————————————
SELECT * FROM
(select A.Name,CountryID as CountryID,C.Name as Country
from Allowance as A
left join Country As C on(A.CountryID=C.Id))AS Temp1
PIVOT (max(Country) For CountryID in([1],[2],[3])) as X
———————————————————————
OutPut
——————————————————————–
NAME 1 2 3
——————————————————————–
Allowance 1 India United States NULL
Allowance 2 NULL United States Japan
——————————————————————-
Here I got only 3 COUNTRY Columns ,But I need N number of Columns with name like Country1 … Country2…..country..N
How it is possible ,
Please help me.
Thanks & kind regards
Mohandas Pk
Hi!
Can we create pivot table with Multiple columns and each column contains multiple rows.
For example………..
Database Table:
BatchID BatchName Chemical Value
——————————————————–
BI-1 BN-1 CH-1 1
BI-2 BN-2 CH-2 2
——————————————————–
This is the table , i need to display like below in Excel Sheet
BI-1 BI-2
BN-1 BN-2
—————————————–
CH-1 1 null
——————————————
CH-2 null 2
——————————————
Here BI-1,BN-1 are two rows in a single columns i need to display chemical value as row of that.
Could Please help me to solve this problem.
Thank You.
I need to display data in dashboard by week date in column but date would be change not fix , user can change different week date and dynamically see data , is it possible using pivot or any other way to do.
In this case you need to use dynamic pivot. Refer this post for more infrmations
hi,
it is very useful for me. i did know the pivot concept.
i could not find on web solution for this but i found myself , i use cursor and update column in horizontal , thanks
I have a table in the follwing format
UserId Email TrainerName NoOfPatients
1 x a 1
2 y b 2
3 z c 3
How do i get tha table in format below
a b c
UserID
Email
NoOfPatients
nice example, now completely got a picture of how pivot and unpivot works, thanks a lot…………..
Friends,
I hope in the comming versions of SQL we will have the pivot in a simpler syntax.
I am not complaining, it has got much better in 2005, compared to 2000.
But I hope to have some thing like
select field_to_group from myTable pivot by verCol, horCol
Hi,
I am using pivot query in table. but i don’t require any order by sorting in that. when i remove order by from pivot query. still it make order by on Product.
@Vishal
Can you use OVER Partition Function in your select statement inside PIVOT to create a unique number for every record, the order will be as is. And then order by that unique number when displaying, so you will see records in the order they were present inside the database.
Let us know if you need more info, Also next time please share sample script with sample input data and expected output
~Peace.
Hi Imran,
Thanks for reply.
Look at the above example of the blog. in this scenario if you remove order by “PRODUCT” then it still sort record by product.
And as you suggest i am using Partition function it will not display proper result. i.e. This will create 2 rows for “BEER”. In first row it display record for “FRED” and in Second row display record for “KATE”.
The query which i tried is:
SELECT PRODUCT, FRED, KATE, RANK
FROM (
SELECT CUST, PRODUCT, QTY, ROW_NUMBER() OVER(Partition BY PRODUCT ORDER BY PRODUCT) as Rank
FROM Product) up
PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt ORDER BY Rank.
Please make a note – in my case all the columns are of varchar type.
@Vishal.
I forgot, we have to specify Order by Clause in OVER PARTITION FUNCTION. This does not solves your problem.
Okay, Let me ask this, What is the order you want to display data in output. Like order by Date time field ?
~ Peace.
Hi Imran
Actually i don’t want any sorting. I just want data without any sorting.
Let me explain briefly
I have table structure like
Patient Id, Date, Temperature, Blood Pressure, Heart Rate etc as my columns.
I want output like all the Date values will be my columns and rest of the columns should becomes my row.
So first i have Unpivoted it which results me as all the columns become my rows with column name as “Name”, “Value” and “Date”.
i.e.
(Unpivoted Result Table)
Name Value Date
———————————————————–
Temperature 35 12/1/2010
Blood Pressure 110 16/7/2010
Heart Rate 70 12/1/2010
Temperature 37 16/1/2010
Then i have pivoted it with Date. It works fine. but it automatically sort by “Name” which i don’t want. I want the same sequence which i got in unpivoted table.
i.e.
(Pivoted Result Table) – Which i got by default order
Name 12/1/2010 16/7/2010
———————————————————–
Blood Pressure NULL 110
Heart Rate 70 NULL
Temperature 35 37
My expected pivoted result.
Name 12/1/2010 16/7/2010
———————————————————–
Temperature 35 37
Blood Pressure NULL 110
Heart Rate 70 NULL
I hope you are now cleared with my query.
Thanks.
Hi Pinal,
I need your help…
my table structure is in the below format
PFAccountNo employee_name SalaryForTheMonth PF
————————————————– ———————-
54544 KANCHAN 2010/jun/18 780
5456 JAYANTHI 2010/jun/18 780
54544 KANCHAN 2010/jul/18 720
5456 JAYANTHI 2010/jul/18 680
54544 KANCHAN 2010/aug/18 700
5456 JAYANTHI 2010/aug/18 620
and I need the Datas in
PFAccountNo employee_name jun_PF July_PF Aug_PF
————————————————– ———————-
54544 KANCHAN 780 720 700
5456 JAYANTHI 780 680 620
You need to use a PIVOT operator. Refer this post for more informations
Hi,
i have result set in this format
Sorce YearMonth CountEntery
A 2003-04 43
B 2003-04 196
A 2003-05 135
B 2003-05 507
i want to pivot it to
Source 2003-04 2003-05
A 43 135
B 1964 5079
can any one help me how to get this