SQL SERVER – PIVOT Table Example « Journey to SQL Authority with Pinal Dave

SQL SERVER – PIVOT Table Example

May 22, 2008 by pinaldave

This is quite a popular question and I have never wrote 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;

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | Tagged PIVOT, UNPIVOT | 21 Comments

21 Responses

on June 3, 2008 at 6:47 pm Dugi

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!
on June 9, 2008 at 10:32 pm SQL SERVER - UNPIVOT Table Example Journey to SQL Authority with Pinal Dave

[...] 29, 2008 by pinaldave My previous article SQL SERVER – PIVOT Table Example encouraged few of my readers to ask me question about UNPIVOT table. UNPIVOT table is reverse of [...]
on June 12, 2008 at 2:35 pm Darshan shah

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 ?
on July 22, 2008 at 8:20 am Robin

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.
on July 22, 2008 at 8:21 am 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
on December 7, 2008 at 7:29 pm SQL SERVER - Example of PIVOT UNPIVOT Cross Tab Query in Different SQL Server Versions Journey to SQL Authority with Pinal Dave

[...] SERVER – PIVOT and UNPIVOT Table Examples SQL SERVER – PIVOT Table Example SQL SERVER – UNPIVOT Table [...]
on February 4, 2009 at 12:29 am aaron

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
on February 4, 2009 at 10:53 am Imran Mohammed

@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
on February 5, 2009 at 7:04 am aaron

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
on February 5, 2009 at 7:18 am aaron

(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
on February 25, 2009 at 4:53 pm SQL SERVER - PIVOT and UNPIVOT Table Examples Journey to SQL Authority with Pinal Dave

[...] Reference : Pinal Dave (http://blog.SQLAuthority.com), SQL SERVER – UNPIVOT Table Example, SQL SERVER – PIVOT Table Example [...]
on March 11, 2009 at 3:10 am xomaly

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
on March 11, 2009 at 7:55 pm Brian Tkatch

@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;
on March 12, 2009 at 1:49 am xomaly

THANK YOU
YOU FINALLY SOLVE MY DOUBTS
THANK YOU VERY MUCH
on March 26, 2009 at 3:12 pm kabil

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
on April 14, 2009 at 11:08 am Sreedhar

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
on April 14, 2009 at 11:09 am 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
on June 1, 2009 at 5:35 pm Mahdestiny

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
on June 2, 2009 at 6:56 am Imran Mohammed

@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.
on June 2, 2009 at 12:23 pm Mahdestiny

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.
on June 2, 2009 at 9:15 pm Imran Mohammed

@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.