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
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
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
@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
@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.
@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.
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.
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…
Hi,
How can i do the Pivoting in SQL Server 2000.
any suggesion or reference link is appreciable.
Thanks in advance
Thanks Imran
Your Script saved me sooo much time.
Truly appreciated.
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..???
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
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
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?
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…….
Hi
i want to create a crystal report using Cross tab, do you have any idea to how to create in sql 2005
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
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.
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
what is mean of FOR keyword in Pivot tables?
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])
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.
Refer this dynamic PIVOT posting