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
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): http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx
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?
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
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
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
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
Build a dynamic query instead, this will work for you :)
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
Hello,
If you are using SQL serer 2005 then see my below article:
http://blog.sqlauthority.com/2008/05/22/sql-server-pivot-table-example/
For SQL server 2000, you can get details to write query to get such reault on Pivot page in BOL.
Regards,
Pinal Dave
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
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx
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
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
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
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
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
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
It was a nice article Dave,Thanks.For those looking for alternative solution below is the solution:
it can also be done using case statement
select Product,
max(case cust when ‘fred’ then Qty end) as ‘FRED’,
max(case cust when ‘kate’ then Qty end) as ‘KATE’
from Product
group by Product
This helped me most considerably.
Very much useful this article .
Very much useful article!.
Is there any way to make something like pivot without using aggregate functions? I need to see the details, not the sum. How can i do something like a crosstable in SQL Server?
Please post some sample data with expected result
how can I round an aggregated columns value?
holas necesito su ayuda kisiera unir dos tablas o mas y que los campos de las tablas no me salgan en forma horizontal , ejemplo :
select tabla_prueba .id,nombre,apellido,tabla2.id,pieza,grupo from tabla_prueba
INNER JOIN tabla2 ON tabla_prueba.id=tabla2.id
este es el resultado de la consulta
id,nombre,id,nombre
1 asas 3 ad
2 zxzx 4 ads
quisiera ke salgan asi
id,nombre
1 asas
2 zxzx
id,nombre
3 ad
4 ads
Nice post!
Can we avoid to sort the data in the pivot as pivot sorts the data autmatically
Nice Article.. for those who have not used pivot before.
Can we avoid to sort the data in the pivot as pivot sorts the data autmatically
help please
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.
What if I have only one column in mytable and i want the values to be displayed horizontally??
eg. table ‘ONE”
column
data
1
2
3
I want to display as :
data 1 2 3
??