This is quite a popular question and I have never written about this on my blog. A Pivot Table can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. The PIVOT operator turns the values of a specified column into column names, effectively rotating a table.
USE AdventureWorks GO SELECT [CA], [AZ], [TX] FROM ( SELECT sp.StateProvinceCode FROM Person.Address a INNER JOIN Person.StateProvince sp ON a.StateProvinceID = sp.StateProvinceID ) p PIVOT ( COUNT (StateProvinceCode) FOR StateProvinceCode IN ([CA], [AZ], [TX]) ) AS pvt;
“If we PIVOT any table and UNPIVOT that table do we get our original table?”
I really think this is a 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 the original data back
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.
Reference: Pinal Dave (https://blog.sqlauthority.com)
79 Comments. Leave new
Nice, but we have too many cases with PIVOT, can you explain in general some 4-5 situation ( don’t recommend Books Online plz everyone we know about it)!
the table example:
IDj
YEAR
OFFICE
ITEMS
PRICE
QTY
COMMENTS
and now the data should look like this:
OFFICE = ROWS
ITEMS = COLUMNS
SUM(QTY) = DETAILS
and
GROUPING BY YEAR OR FILTERING BY YEAR
thnx!
hi Pinal I understood the meaning of pivot query
but if i have around 100 of departments and i want to use pivot query for this to find out the avg sal departmentwise. so what to to for this ?
Is it possible to use two aggreation in the PIVOT? If possible, can you please suggestion how to use it?
e.g
SUM(Price), Count(Product)
FOR ProductId IN ( [1],[2],[3] )
Thank and regards,
Robin.
Forgot one thing that. I did by using JOIN statement. It is working but I would like to know better way.
Thank for your help.
Regards,
Robin
How to PIVOT table with date column?
Source table:
==========
SiteNo SubNo MV Date
—— ——– — ————
001 SUB-028 V1 2007-03-11
001 SUB-028 V2 2007-05-21
001 SUB-028 V3 2007-06-22
001 SUB-028 V4 2007-07-11
001 SUB-028 V5 2007-08-05
001 SUB-028 V6 2007-09-28
002 SUB-010 V1 2007-04-22
002 SUB-010 V2 2007-05-22
002 SUB-010 V3 2007-06-05
Result table:
===========
SiteNo SubNo [V1] [V2] [V3] [V4] [V5] [V6]
—— ——— ———– ——— ———- ———– ———– ———-
001 SUB-028 2007-03-11 2007-05-21 2007-06-22 2007-07-11 2007-08-05 2007-09-28
002 SUB-010 2007-04-22 2007-05-22 2007-06-05
@Aaron.
It took nearly one and half hours for me to write these 3 lines script. The main hurdle came at usage of aggregate functions used in Pivots.
Because I have to use a function with Pivot, there was no way I can display date values in V1. V2 …. V6 columns.
I have used Max function. Since you have only one date for one combination of V1 and SiteNo, this will work fine in your case, but in general this could be misleading…. as we are asking database engine to pick up max values … anyways here is your query.
SELECT SiteNo, subno ,[V1] ,[V2] ,[V3] ,[V4] ,[V5] ,[V6]
FROM (
SELECT SiteNo, subno, MV, Date
FROM SourceTable) up
PIVOT ( max(Date) FOR MV in (V1 ,V2 ,V3 ,V4 ,V5 ,V6 ) )AS pvt
ORDER BY SiteNo
I have tested Output for this script , it does matches with your expected Output.
Regards,
IM
Hi *Imran Mohammed*, it’s so great you worked it right. I was struggling on what function should be used too. Yes, in the case I posted, it works perfectly.
As you already see that actually I do have case like below, which for one MV value there’re more than one dates. I wonder if this can be done by PIVOT. If not, any other option?
SiteNo SubNo MV Date
—— ——– — ————
001 SUB-028 V1 2007-03-11
001 SUB-028 V1 2007-03-15
001 SUB-028 V1 2007-03-19
001 SUB-028 V2 2007-05-21
001 SUB-028 V3 2007-06-22
001 SUB-028 V4 2007-07-11
001 SUB-028 V4 2007-07-17
001 SUB-028 V4 2007-07-21
001 SUB-028 V4 2007-07-29
001 SUB-028 V5 2007-08-05
001 SUB-028 V6 2007-09-28
002 SUB-010 V1 2007-04-22
002 SUB-010 V2 2007-05-22
002 SUB-010 V3 2007-06-05
002 SUB-010 V3 2007-07-05
(continue last post)
The challenge is that MV value could be any string, could be V1 ~ Vx. And for Same MV value, they just want to list all dates, like
SiteNo SubNo [V1] [V1] [V1] [V2] [V3] [V4] [V5] [V6]
—— ——— ———– ——— ———- ———– ———– ———- ———— —————-
001 SUB-028 2007-03-11 2007-03-15 2007-03-19 2007-05-21 2007-06-22 2007-07-11 2007-08-05 2007-09-28
Hi PLS HELP ME, i have a table like that
d a b c a1 b2 c3
401 0.3 0.8 0.9 z x y
402 0.4 0.5 0.6 x z y
And i need this result in my query
d z x y
401 0.3 0.8 0.9
402 0.5 0.4 0.6
I was thinking on pivot tables but i dont know how to do it with more colums
@xomaly i do not understand your question. So, i guessed. How about this:
WITH
Data
AS
(
SELECT 401 d, 0.3 a, 0.8 b, 0.9 c, ‘z’ a1, ‘x’ b2, ‘y’ c3 UNION ALL
SELECT 402 d, 0.4 a, 0.5 b, 0.6 c, ‘x’ a1, ‘z’ b2, ‘y’ c3
)
SELECT
d,
CASE a1
WHEN ‘z’ THEN a
WHEN ‘X’ THEN b
WHEN ‘y’ THEN c
END,
CASE b2
WHEN ‘z’ THEN a
WHEN ‘X’ THEN b
WHEN ‘y’ THEN c
END,
CASE c3
WHEN ‘z’ THEN a
WHEN ‘X’ THEN b
WHEN ‘y’ THEN c
END
FROM
Data;
THANK YOU
YOU FINALLY SOLVE MY DOUBTS
THANK YOU VERY MUCH
hi..i ve written one query it return the following results
date [name]
06:00 aaaa
06:00 bbbb
06:00 ccccc
06:30 dddd
06:30 eeee
06:30 ffffffff
06:30 gggg
07:00 hhhh
08:00 NULL
08:30 NULL
09:00 NULL
09:30 IIIIII
10:00 NULL
.
.
..
i want to display this in the following format
date name
06:00 aaaa bbbb cccc NULL
06:30 dddd eeee ffffff gggg
07:00 hhhh NULL NULL NULL
08:30 NULL NULL NULL NULL
09:00 NULL NULL NULL NULL
10:00 IIIIII NULL NULL NULL
.
.
.
.
is it possible using pivot? kindly help me
select date,(select name+’ ‘ from tbl b where a.date=b.date) from tbl a
Hi Pinal,
You’re examples on pivot and unpivot are very good. However I have some questions which may or may not have to use this. Please suggest with your experience which is suitable for me.
I have a TABLE in oracle as
Australia Entry1 Apple $ 2
Japan Entry2 Guava $2
Australia Entry2 Guava $2
Hungary Entry3 Guava $2
Australia Entry2 Apple $2
Japan Entry1 Guava $2
Australia Entry4 Orange $2
Japan Entry2 Orange $2
My Output should be as follows where Amount is added w.r.t Entries as well as Fruits but in columns.
Country Entry1 Entry2 Entry3 Entry4 Apple Guava Orange
Australias $2 $4 0 $2 $4 $2 $2
Japan $2 $4 0 0 0 $4 $2
Hungary 0 0 $2 0 0 $2 0
I would like the output as above. However I do not want to use decode, which is what I am using right now.
Is it possible to use this for a varible number of columns. Basically the number of columns will not be more than 10 for sure, but I need them to be variable which is not possible by using decode.
Thanks for all your help.
Sreedhar
THis is related to above question. I can move the table to sql if needed. If I know I can get it easier from there.
THanks,
Sreedhar
Hi…
I have a table : T1
No ID Date Value
___________________________________
1 1001 01/05/2009 101.00
1 1001 15/05/2009 102.00
1 1001 20/05/2009 105.00
2 1001 01/05/2009 41.00
2 1001 15/05/2009 44.00
3 1001 01/06/2009 330.00
I need the Data in this Format
No ID 01/05/2009 15/05/2009 20/05/2009
01/06/2009
____________________________________________________
1 1001 101.00 102.00 105.00 NULL
2 1001 41.00 44.00 NULL NULL
3 1001 NULL NULL NULL 330.00
can you give me the solution for this type…
Thanks in advance
@Mahdestiny
This is the script.
— Create Table
create table T1 ( [No] int, [ID] int, Date Datetime, Value decimal (38,2))
— Insert data into the table
insert into T1 values (1, 1001, ’05/01/2009′, 101.00)
insert into T1 values (1, 1001, ’05/15/2009′, 102.00)
insert into T1 values (1, 1001, ’05/20/2009′, 105.00)
insert into T1 values (2, 1001, ’05/01/2009′, 41.00)
insert into T1 values (2, 1001, ’05/15/2009′, 44.00)
insert into T1 values (3, 1001, ’06/01/2009′, 330.00)
— Check the Data
select * from T1
— Using Pivot key word to get required output
SELECT [No] , [ID] ,[05/01/2009] ,[05/15/2009] ,[05/20/2009] ,[06/01/2009]
FROM (
SELECT [No], [ID], [Date], [Value]
FROM T1) up
PIVOT ( sum([Value]) FOR [Date] in ([05/01/2009] ,[05/15/2009] ,[05/20/2009] ,[06/01/2009]) )AS pvt
~ IM.
Here your using
“SELECT [No] , [ID] ,[05/01/2009] ,[05/15/2009] ,[05/20/2009] ,[06/01/2009]
FROM ( ”
i cant say how many dates will be coming from select statement.
coz im using Select * from T1, this will return me many Date.
Also im not using sum([Value]) FOR [Date] wise.
I need to show dynamically date as column and value as row using pivot.
@Mahdestiny,
Then you need to write Dynamic SQL.
First prepare the select statement with Dynamic SQL.
Once you prepare the select statement, then execute that statement.
~ IM.
Suppose , Table Sales has 10 product ,I want to Top 5 Sales product
and sum of the rest of product in single select Query
Hello,
I have a table ITEM_MST
ITEM_TYPE QTY Date
1 2 15 July 2009
1 4 16 July 2009
2 3 17 July 2009
2 1 18 July 2009
1 4 19 July 2009
1 6 20 July 2009
1 2 21 July 2009
I wan the output in this format
ITEM_TYPE QTY1 QTY2 QTY3
1 2 4 4
1 6 2
2 3 1
I dont want to use any aggregate functions as there is nothing to aggregate.
Please let me know the solution for the same ASAP.
Thanks and Regards
Rahul