SQL SERVER – PIVOT Table Example | SQL Server Journey with SQL Authority

« SQL SERVER – 2005 – Twelve Tips For Optimizing Sql Server 2005 Query Performance

SQL SERVER – SQL SERVER – UDF – Get the Day of the Week Function – Part 2 »

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)

About these ads

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

68 Responses

on June 3, 2008 at 6:47 pm | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply xomaly

THANK YOU
YOU FINALLY SOLVE MY DOUBTS
THANK YOU VERY MUCH
on March 26, 2009 at 3:12 pm | Reply 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 December 24, 2012 at 2:55 pm | Reply vickysatya
  
  select date,(select name+’ ‘ from tbl b where a.date=b.date) from tbl a
on April 14, 2009 at 11:08 am | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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.
on July 22, 2009 at 12:43 pm | Reply mostafiz

Suppose , Table Sales has 10 product ,I want to Top 5 Sales product
and sum of the rest of product in single select Query
on September 25, 2009 at 9:35 am | Reply RAHUL

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
on September 26, 2009 at 8:53 pm | Reply Gary

Hi, Pinal, Robin

Any further words on using two aggreations in the PIVOT?
Or is it possible to use an expression in the place of aggreation function? something like (SUM(SellAmount) – SUM(CostAmount))/SUM(SellAmount)*100,
instead of a single native function.

thanks a lot
on September 27, 2009 at 3:45 am | Reply Peter

sorry for that post: to keep it short;

table:

PARAM VALUE
k4401 AAA
k4402 BBB
k4403 CCC

Output would look like:

k4401 k4402 k4403
AAA BBB CCC

thanks in advance
on September 27, 2009 at 11:13 am | Reply Imran Mohammed

@Peter

create table #Example ([PARAM] varchar (40), [VALUE] varchar (40))

insert into #Example values (‘k4401′, ‘AAA’)
insert into #Example values (‘k4402′, ‘BBB’)
insert into #Example values (‘k4403′, ‘CCC’)

SELECT [k4401], [k4402], [k4403]
FROM
(SELECT [PARAM], [VALUE]
FROM #Example) p
PIVOT
(
MAX ([VALUE])
FOR [PARAM] IN
( [k4401], [k4402], [k4403])
) AS pvt

~ IM
on September 27, 2009 at 11:18 am | Reply Imran Mohammed

@Gary,

Why don’t you make a computed column in your select list, with what ever expression you have and then name that column , and then in pivot, just use max expression.

Just give us some sample data, input, and expected output.

~ IM.
on September 27, 2009 at 11:30 am | Reply Imran Mohammed

@Rahul.

I need more information for your input and output, your output is confusing, please post complete output.

Here is a sample script.
SELECT ITEM_TYPE, [1], [2], [3], [4], [5], [6], [7]
FROM
(SELECT ITEM_TYPE, QTY , QTY AS QTYY
FROM Example4) p
PIVOT
(
MAX (QTYY)
FOR QTY IN
( [1], [2], [3], [4], [5], [6], [7] )
) AS pvt
ORDER BY VendorID;

What I see from your procedure, we can do it through another procedure or script with a while loop, I dont know if that could be done through a simple select statement.

~ IM.
on November 29, 2009 at 8:12 am | Reply Alice Hsu

VendorName CostCenter DT Amount
ANCHOR SAFETY INC 91401 5/9/2009 22.58
ANCHOR SAFETY INC 91401 7/9/2009 63.38
AMERICAN TOWER 91442 6/9/2009 18.7
AMERICAN TOWER 91442 8/9/2009 19.19

If I have the data as above and I like to get the following result:

ANCHOR SAFETY INC 91401 5/9/2009 28.58 7/9/2009 63.38
AMERICAN TOWER 91442 6/9/2009 18.7 8/9/2009 19.19

I tried to use PIVOT in SQL Server 2005 but failed.

Please help.

Thank you.
on December 15, 2009 at 8:05 pm | Reply Max

Yeah, pivoting tables are fine and dandy, but when it comes to reporting, (And this is the only case I really needed pivot), most of the reporting tools can not deal with pivots or can not handle unknown number of columns in output data.. etc.
So I had to make up other solutions…
on December 30, 2009 at 6:38 pm | Reply Kajal Shah

Hi,

How can i do the Pivoting in SQL Server 2000.
any suggesion or reference link is appreciable.

Thanks in advance
on January 5, 2010 at 11:05 pm | Reply Roberto

Thanks Imran

Your Script saved me sooo much time.

Truly appreciated.
on February 16, 2010 at 12:46 pm | Reply Manivannan

Hi… I have a Question.. How to write a query for following task..

INPUT

Name ENG TAM MAT
————————–
RAJ 52 65 72
RAM 45 98 75
JOHN 78 88 52

I want the output as

Name RAM
—————–
ENG 45
TAM 98
MAT 75

Can anyone help me..???
on March 24, 2010 at 11:35 am | Reply Chiranjiv Khemka

Hi All,

I too need the pivoting the below table.

Current Result by the Query :

SELECT Genset_Master.EngNo, Genset_Master.AltNo, Genset_Master.CustName, Service_Master.SiteAdd,
Servicing_History.ServDate
FROM Service_Master INNER JOIN
Genset_Master ON Service_Master.Genset_Id = Genset_Master.Genset_Id INNER JOIN
Servicing_History ON Service_Master.Genset_Id = Servicing_History.Genset_Id

==========

EngNo AltNo CustName SiteAdd ServDate
————– — —————————————————-
001 Alt1 Cust1 Site1 2007-03-06
001 Alt1 Cust1 Site1 2007-05-11
002 Alt2 Cust2 Site2 2007-08-05
002 Alt2 Cust2 Site2 2007-08-12

I want the result in below format

EngNo AltNo CustName SiteAdd ServDate1 ServDate 2
— ———————————————————————-
001 Alt1 Cust1 Site1 2007-03-06 2007-03-11
002 Alt2 Cust2 Site2 2007-08-05 2007-08-12

Please guide me to create query for above result
on April 7, 2010 at 1:35 pm | Reply Fazal Vahora

Hi,Pinal Sir

is it Possible to use morethan one aggregate function in Pivot table?

Like

Select Count(no),sum(value1+value2) ,sum(value3+value4)

Please Give me solution

Thnaks in Advance
on May 13, 2010 at 11:40 pm | Reply Arnon

Thanks for the article. I have a question:
Using the example in the article, I want to summarize the data for all states, not just the three, without explicitly listing them. something like:

….FOR StateProvinceCode
IN (StateProvinceCode FROM Person.Address)….

This does not work, I get an error message saying “expecting ID, QUOTED_ID or ‘.’

Any ideas?
on May 27, 2010 at 8:03 am | Reply fina_el

Hi Mr Pinalkumar Dave,
This article are very good.
However I have some questions which may or may not have to use this.
Please support with your experience which is suitable for me.

I have a table” ProdSchedule
Model Date upload Lot_qty Prod_Date
AAA 2010-05-14 1500 2010-05-15
AAA 2010-05-14 2500 2010-06-18
AAA 2010-05-21 3500 2010-06-01
AAA 2010-05-21 4500 2010-05-25
BBB 2010-05-14 5500 2010-05-05
BBB 2010-05-21 6500 2010-06-04

In this case, i need to compare the lot_qty on period month(get from prod_date)
at every week (this week and last week).

And I need the output like this format:

Model Date upload May June
AAA 2010-05-21 4500 3500
AAA 2010-05-14 1500 2500
DiffA 3500 1000
BBB 2010-05-21 5500 0
BBB 2010-05-14 0 6500
DiffB -5500 6500

Thank You…….
- on June 2, 2010 at 9:04 pm | Reply terance
  
  Hi
  i want to create a crystal report using Cross tab, do you have any idea to how to create in sql 2005
on November 22, 2010 at 10:54 pm | Reply Praveen Poosapati

Hi,

I have a table like this..

Product element value
1 FOB 16
1 Freight 0.25
1 Burden 16.25
2 FOB 16
2 Freight 0.25
3 Burden 16.25
on November 22, 2010 at 11:05 pm | Reply Praveen Poosapati

Hi,

I have a table like this..

Product element value

1 FOB 16
1 Freight 0.25
1 Burden 16.25
2 FOB 16
2 Freight 0.25
2 Burden 16.25
2 FOB 18
2 Freight 1.75

Now I am writing a query for the pivot table as ..

SELECT *
FROM
(SELECT PRODUCT, ELEMENT, VALUE
FROM source_table (NOLOCK)
) as Data
PIVOT
( AVG(VALUE) FOR ELEMENT IN (WHOLESALE, [FOB], [FREIGHT], [BURDEN])
)Pivot_Table

I am getting this as a result.

PRODUCT FOB FREIGHT BURDEN
1 16 NULL NULL
2 17 NULL NULL

I am getting nulls for the Freight and Burden because (I Think) they have decimal values.

Can some one help me out in this and tell me how I can get the desired values for these decimal types also?

Thanks.
-Praveen.
on March 3, 2011 at 2:57 pm | Reply debojyoti

Hi All,

The pivot will not work when the columns are varchar fields .

data

tick rate
FIT AAA
FIT AA+
SN AAA
SN AA+
SN D
MO Ca
MO C

I wanna see
FIT SN MO
AAA AAA Ca
AA+ AA+ C
on April 12, 2011 at 9:41 pm | Reply Jasmeet Kaur

what is mean of FOR keyword in Pivot tables?
on May 13, 2011 at 6:18 am | Reply Akhtar jahan

Hi Jasmeet,
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;

FOR means WHERE. IN this case

select COUNT(StateProvinceCode)
FROM P
WHERE StateProvinceCode
IN ([CA], [AZ], [TX])
on May 13, 2011 at 9:57 am | Reply Calvin

You uses [CA], [AZ], [TX] because you know what is the column,

what if the column is dynamic? Users can add new items at any time.
- on May 16, 2011 at 3:52 pm | Reply madhivanan
  
  Refer this dynamic PIVOT posting
  http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
on September 14, 2011 at 3:34 pm | Reply Dhanashree

what is the query of converting column fields into row fields by using pivot table?
on December 22, 2011 at 7:54 pm | Reply Prithvi Nath Pandey

great , that done my job easy
on January 21, 2012 at 2:07 am | Reply bhagat

hi pinal,

I have doubt can you please help me..

Required output in this format.

empid GS NS MS AS total
1001 3 2 1 2 8
1002 1 4 3 0 8

Below is the table structure
empid Shift Date
1001 GS 01-Jan-2012
1001 GS 02-Jan-2012
1001 GS 03-Jan-2012
1001 NS 04-Jan-2012
1001 NS 05-Jan-2012
1001 MS 06-Jan-2012
1001 AS 07-Jan-2012
1001 AS 07-Jan-2012
1002 GS 01-Jan-2012
1002 NS 02-Jan-2012
…..
…..
….
1002 AS 07-Jan-2012
etc…

Please help me to solve this issue… It’s really urgent
if you can reply early I’ll really appreciate
on February 2, 2012 at 12:58 am | Reply vinay

Hi

I have used Pivot in my Stored procedure.

Like while I was executing, there was a bit server blockings . Is it really due to that. And as per performance point of which is better While Looping is that better or PIVOT is that better.
on February 21, 2012 at 8:00 pm | Reply Vishal Misquitta

Good Post
on February 21, 2012 at 8:06 pm | Reply Vishal Misquitta

Good Post….
on March 22, 2012 at 9:39 pm | Reply Kashif

Hi

I am looking for a dynamic query which will populate the multiple records from a column into a row with a unique article id. e.g.

FROM

Art_Id Product
101308 BD258
101309 BD259
101301 BD214
101308 BD345
101309 MD852

…. more than 2million records

TO

ART_ID COL1 COL2 …………. n column
101308 BD258 BD345
101301 BD214
101309 BD259 MD852

WHEREVER I HAVE SEEN THE HELP BLOG ON IT IT GOT CALCULATION INVOLVED BUT IN MY CASE I DO NOT HAVE ANY CALC. JUST WANT TO TRANSPOSE MY TABLE.

THANKS IN ADVANCE FOR HELP
on April 12, 2012 at 9:56 am | Reply Nibedita

sir i want a table like this. my base table is empid,cellphno,officeno,residenceno.but i want data like this manner
empid,number,type. in number column it display all the three no of empid 1 then it display empid 2.in type if it is cellph it show ‘c’ , if officeno it shows ‘o’ like this.
please help me…
on April 16, 2012 at 6:04 pm | Reply Ryu-Jin Kurozuka

How To Create a Pivot Table for this one

1 1 1 500
2 1 2 300
3 2 1 300
4 3 2 400
5 3 1 8000
on June 11, 2012 at 10:41 am | Reply Jyothi

hourname sublistTitle Day
1stHour Social Monday
2nd Hour Maths Monday
1stHour Telugu TuesDay
3rd Hour Social WedDay
1stHour Social FriDay
3rd Hour Computers FriDay

query:

select ch.hourname,dta.sublistTitle,(select d.DayName from DayTable d where d.dayid=t.dayid) as Day from timetable t,classhours ch,(select c.cstuid as cstuid,sl.sublistTitle as sublistTitle from subjectslist sl,subjects_school ss,cst c where sl.sublistid = ss.sublistid and ss.schoolsubuid=c.schoolsubuid and c.suid = 1030) dta where t.houruID = ch.classHouuID and dta.cstuid = t.cstuid and t.classrumid=1004

I need to get

Day isthour 2nd hour 3rd hour 4th hour
Monday Social Maths Null NUll

Tuseday Telugu Null Null Null

Wed Null Null Social NUll

Thursaday NUll Null NUll Null

Friday Social Null Computers NUll

Please its Really very important and very urgent so kindly help me as esarly as possible today

Thaking you

jyothi
on June 29, 2012 at 8:39 am | Reply Kevin

I have these tables.

table1:

IDNumber LName Fname Book Section Title
11355 Smith John CCC 2145 General Idea
11311 Doe John BBB 2446 General Health
11433 Doe John DDD 2554 College Politcs
11377 Brown Ben FFF 2610 How to survive
11388 Cam Rachel SSS 1274 Hapiness
11344 Caml Rachel HHH 2845 Solution
AND

table 2:

IDNum Q01 Q02 Q03 Q04 Q05 Q06 Q07
11355 5 5 5 5 5 5 5
11355 5 5 5 5 5 5 5
11355 4 5 4 4 5 4 4
11322 4 4 4 4 5 4 5
11322 5 5 5 5 5 5 5
11300 5 5 5 5 5 5 5
11300 3 4 3 5 5 4 3
11300 5 5 5 5 5 5 5
11300 5 5 5 5 5 5 5
11300 4 4 4 4 5 4 4
11311 3 5 4 4 5 5 5
11311 5 5 5 5 5 5 5
11377 3 2 3 5 5 5 4
11377 5 5 5 5 5 5 5

I need to have a result:

table 3:

IDNum QID Lname Room Section Title
11355 Q1 Smith CCC 2145 General Idea
11355 Q2 Smith CCC 2145 General Idea
11355 Q3 Smith CCC 2145 General Idea
11355 Q4 Smith CCC 2145 General Idea
11355 Q5 Smith CCC 2145 General Idea
11355 Q6 Smith CCC 2145 General Idea
11355 Q7 Smith CCC 2145 General Idea
11322 Q1 Doe BBB 2246 General Health

Thank you,
on July 10, 2012 at 6:32 pm | Reply Parag

Suppose…I have the following rows in a table MEMBERSHIP_NO, CLASS, LASTNAME, FIRSTNAME, MiddleName, HOME_ST, HOME_CITY, HOME_PROV_STATE, HOME_CTRY, HOME_PC, HOME_PHONE, POSITION with their values. I want to use Pivot on these columns but these rows might not be present for each of the MemberIDs. Some columns are missing for some members depending on the data availability. How can I handle the missing data rows (columns) in my Pivot Report?
on August 6, 2012 at 2:00 pm | Reply Plahanov

Hi ,

Its really great post. I think i can solve my current problem with PIVOT.

Can anyone please help how to do this with PIVOT. I also tried but didnt get the exact out put what i want.

Available Data

EmpCode PayCode Value
————————————————
E001 HRA 2000
E001 BASIC 45000
E001 TA 5000

The output format how I want

EMPCODE HRA BASIC TA
——————————————————————
E001 2000 45000 5000

Thanks in advance.

Plahanov
on August 15, 2012 at 5:50 pm | Reply bubai banerjee

Hi Pinal Sir,
I have doubt can you please help me..I am very new in s/w DEvelopment. below i am describing.

Original Table Structure.

ID Date TimeLogged UserName
1 10/8/2012 5.5 Bubai
2 11/8/2012 2.3 Bubai
3 10/8/2012 3.3 Bhanu
4 11/8/2012 7.3 Bhanu

I want result like below. User Name should be dynamic.May be lot of users.User name will come from Database table. Please give me broad description as I am very new in the system,for my understanding.

Date Bubai Bhanu Total
10/8/2012 5.3 3 8.3
11/8/2012 2.3 7.3 10
Total 8 10.3 18.3
- on August 23, 2012 at 4:40 pm | Reply madhivanan
  
  You need dynamic PIVOT. Refer this post http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx
on August 31, 2012 at 4:22 pm | Reply Merlin

I having table Employee and Production tables.

To show reports for target and achieved Value for the every month for an employee.

below resulted table example:

Name Date Target Value
Admin 8/17/2012 0 8
Admin 8/18/2012 0 6
Admin 8/22/2012 0 1
Admin 8/23/2012 0 11
Admin 8/24/2012 0 3
Admin 8/26/2012 0 2
Babu 8/17/2012 500 376
Babu 8/18/2012 500 313
Babu 8/21/2012 500 172
Babu 8/22/2012 500 305

Pivot

Name 1T 12T … Nt 1V 2V Nv
Admin 0 0 0 0 8 6 0
Babu 500 500 500 0 376 313 0
.
.

with regards

Merlin.P
on November 27, 2012 at 4:05 pm | Reply saul

Hi i want to ask something about my work

select * from
(Select m.Brand,u.Color,Count(Unit)as [Total unit] from Brands m inner join Products u on m.BrandID=u.ProductID
Group by m.Brand ,u.Color with cube)as Pvt
PIVOT
(
Sum([total unit])
For Color
IN
(
[Black],[White],[Gray],[Silver],[Orange],[Dark Blue]
)
)AS Pivot_Table

I want to show total Brand and Total color but this code just calculate each color and count every color per brand

How can i get Total Brand and Total Color ????

Please help me
on December 12, 2012 at 9:20 pm | Reply Tanveer

I have Table 2
Table A
ShowId,ShowName,
1,Show1
2,Show2
:
:
:
End Not know

DanceId,DanceNumber,DanceName
1,1,1
2,1,2
3,2,1
:
:
:
End Not Know

I am looking for result like

Row 1:Show1,Show2,Show3………………….
Row 2: DaneNumber,DanceNume……
Row 3: DaneNumber,DanceNume……
:
:
How can we get this
on December 14, 2012 at 7:15 pm | Reply Suresh G

I need help
How can access EXEC StoredProcedure1 result set in StoredProceure2
on February 14, 2013 at 1:42 pm | Reply nagarju

hi sir plz help for this problem

In my table the values are stored as
empid ename date status
————————————————
101 abc 01/02/2013 present
101 abc 02/02/2013 present
101 abc 03/02/2013 absent
102 xyz 03/02/2013 present
and so onnnn
101 abc 28/02/2013 present
102 xyz 28/02/2013 present

here i mention only one employe record.There is n number employes there my problem is
how to show table like this

empid ename 1 2 3 4 5 6 …….28
————————————————————————
101 abc present present absent…………. presnt
102 xyz present present present………..present

here 1 2 3 4 represent dates of the month.

plese kindly provide me the query to get like this.
in this above case empid is also varchar

thanks.
on April 19, 2013 at 2:47 pm | Reply Amit Obviously

Nice article Merlin
on May 9, 2013 at 11:15 pm | Reply ladp7

Is it possible to use two or more tables for single pivot function query?