SQL SERVER – PIVOT Table Example

May 22, 2008

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;

SQL SERVER - PIVOT Table Example pivotexample

“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)

PIVOT, SQL Scripts, SQL Server, UNPIVOT

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

79 Comments. Leave new

ladp7
May 9, 2013 11:15 pm
Is it possible to use two or more tables for single pivot function query?
Reply
Dilip
June 22, 2013 3:41 pm
Hi! I have table with column as show below
x Date Noofitems
1 12/06/2013 23
2 12/06/2013 14
3 15/06/2013 50
I need to get the record as follows
x 12 15
————————
1 23 –
2 14 –
3 – 50
Pls help me.
Reply
Mandar
July 8, 2013 6:03 pm
Hello,
I having urgent requirement on Given Data to convert it into another format.
id Qdate Qty POID
1 2013-07-11 1000 33
2 2013-07-12 500 33
3 2013-07-12 500 33
Converting it into Below Format
Quantity Recd. #1 Date1 Quantity Recd. #2 Date2 Quantity Recd. #3 Date3
1000 2013-07-11 500 2013-07-12 500 2013-07-12
Reply
VJ
July 27, 2013 3:49 am
Hi
I am new to Pivot can someone please help with below issue.
Thanks in advance
Table Structure
State_ID Customer_ID Field_Desc Field_Value start_date end_date
1234 50045 Field1 Result 1 00:00.0 00:00.0
1234 50045 Field2 res 2 00:00.0 00:00.0
1234 50045 Field3 res 3 00:00.0 00:00.0
1234 50045 Field4 res 4 00:00.0 00:00.0
Desired Result
State_ID Customer_ID Field1 Field2 Field3 Field4
1234 50045 Result 1 res 2 res 3 res 4
My Result
State_ID Customer_ID Field1 Field2 Field3 Field4
1234 50045 Result 1 NULL NULL NULL
1234 50045 NULL res 2 NULL NULL
1234 50045 NULL NULL res 3 NULL
1234 50045 NULL NULL NULL res 4
My Script
Select State_id,Customer_id,Field1,Field2,Field3,Field4
from #data
PIVOT
( max(field_value)
for Field_Desc in (FIeld1,Field2,Field3,Field4)
) pvt
where customer_id is not null
and state_id=1234
Reply
mohd kamran
September 27, 2013 3:57 pm
How to use cast with aggegatate funtion while doing pivot.
i m trying to do cast to decimal while doing avg in pivot,its throwing error:’cast’ is not a recognized aggregate function.
Reply
gopal
December 22, 2013 9:31 am
I’ve a table for sample name that contains the following columns firmnesssID, SampleTestingID and Firmness:
firmnesssID SampleTestingID Firmness
1 243 1.8
2 243 1.9
… …. …..
21 244 7.9
Now, I need to display output like the below. I’m assuming there are maximum 40 records for a particular SampleTestingID and I want to show Firmness values under column header like 1a 1b 2a 2b 3a 3b … 20a 20b (40 columns) for every distinct SampleTestingID.
SampleTestingId 1a 1b 2a 2b 3a 3b 4a 4b 5a 5b 6a 6b 7a 7b 8a 8b 9a 9b 10a 10b 11a 11b 12a 12b 13a 13b 14a 14b
243 6.70 6.66 6.13 7.33 6.25 7.19 6.81 6.54 7.78 7.59 7.86 9.71 8.40 7.10 7.82 6.46 4.94 6.24
246 7.79 7.14 6.13 6.31 7.68 5.39 9.27 8.53 6.84 6.89 7.05 5.84 4.86 5.97 6.48 5.44 6.70 7.21 7.68 6.86
Reply
- gopal
  December 22, 2013 9:54 am
  I also tried to achieve the same by PIVOT the data but I was unable to get desired output. Here is what I was doing:
  SELECT P.SampleTestingId, IsNull(P.[1], 0) as 1a, IsNull(P.[2], 0) as 1b
  , IsNull(P.[3], 0) as 2a, IsNull(P.[4], 0) as 2b
  , IsNull(P.[5], 0) as 3a, IsNull(P.[6], 0) as 3b
  , IsNull(P.[7], 0) as 4a, IsNull(P.[8], 0) as 4b
  , IsNull(P.[9], 0) as 5a
  FROM
  (
  SELECT SampleTestingId, Firmness,[FirmnessID]
  FROM dbo.ftatestdata WHERE SampletestingId=243 AND Isdeleted=0
  ) I
  PIVOT
  (
  SUM([FirmnessID])
  FOR [Firmness] IN ([1], [2], [3], [4], [5], [6], [7], [8], [9])
  ) as P
  It provides header structure but data displayed 0 always.
  and
  DECLARE @cols NVARCHAR (MAX)
  SELECT @cols = COALESCE (@cols + ‘, [‘ + Convert(varchar, FirmnessID) + ‘]’, ‘[‘ + Convert(varchar, FirmnessID) + ‘]’)
  FROM FtaTestData
  WHERE SampleTestingID=243 AND IsDeleted=0 order by SampleTestingID
  print @cols
  DECLARE @Query VARCHAR(MAX)
  SET @Query= ‘SELECT ‘+ @cols +’ FROM (SELECT FirmnessID, Firmness
  FROM FtaTestData WHERE SampleTestingID=243 AND IsDeleted=0) P PIVOT ( MAX(Firmness) FOR FirmnessID IN ( ‘+@cols+’ ) ) AS pvt’
  print @Query
  EXEC(@Query)
  Reply
pankaj
April 27, 2014 12:49 pm
hello sir , i have a problem how to automatically update sql table. but my requirement is i given date-time suppose (01/01/2022) that particular date update my sql table how?
Reply
v
December 14, 2014 3:32 am
hello sir,
I need a result like below
Student Enlis science social GrandTotal
37 100 50 50 200
I cannot get total using pivot. Can you help plz
Reply
venkataramasiva
January 24, 2015 3:06 pm
Hi Sir,
I need to show results in below format
Question,Partner Name1,partner name2………
First question,Yes,…..,….
Second question,Yes,….,….
In above case questions will remain same, only partner names will increase and there responses.
i hope you will come with solution.
Thanks in advance.
Regards,
Siva.
Reply
CHANDRASEKAR
June 17, 2015 7:00 pm
I WANT TO
1.default table…
=================================
name type date
=================================
sekar present 2015-06-01
sekar present 2015-06-02
sekar present 2015-06-03
sekar absent 2015-06-04
sekar present 2015-06-05
=================================
i want to like this below table…..
======================================================================
name 2015-06-01 2015-06-02 2015-06-03 2015-06-04 2015-06-05
=======================================================================
sekar present present present absent present
========================================================================
Reply
tats
August 10, 2015 4:54 pm
i have a requirement like i need to write update
sample data will be like this
accountnumber tier_name note_1 note_2 note_3
100 jjj hhh kkk ooo
100 hhh
100 kkk
100 ooo
so i have to update note fields dynamically?
anyone can please help me to find out the solution?
Reply
madhivanan
July 6, 2017 9:53 pm
If you want multiple PIVOT columns, you can use this procedure
https://madhivanan.wordpress.com/2017/07/06/dynamic-crosstab-with-multiple-pivot-columns/
Reply
Lenin R
January 21, 2018 1:12 pm
I have table.
id Year groupA,groupB,TypeId, EmpId
1 2016 20 10 1 1010
2 2016 10 15 1 1010
3 2016 15 20 2 1010
4 2017 10 15 1 1011
5 2016 10 10 2 1011
I need colum records like
EmpId Year groupA, groupB, groupC, groupD
1010 2016 30 25 15 20
1011 2016 – – 10 10
1011 2017 10 15 – –
Here based on TypeId it should be as column
Can you please help me for creating query
Reply
Lenin R
January 21, 2018 1:40 pm
RowID EmpID Year GroupA GroupB TypeID
—————————————————————————–
1 1010 2016 10 20 1
———————————————————————————-
2 1010 2016 15 12 1
———————————————————————————-
3 1010 2016 10 15 2
———————————————————————————-
4 1011 2016 25 20 1
———————————————————————————-
5 1011 2016 10 10 2
———————————————————————————-
6 1011 2017 10 15 1
———————————————————————————-
7 1011 2017 30 20 2
———————————————————————————-
8 1011 2017 12 21 2
———————————————————————————-
9 1012 2016 10 40 3
Result Table
—————————————————————————————————————-
TypeID = 1 | TypeID = 2 | TypeID=3
—————————————————————————————————————–
RowID EmpID Year | GroupA GroupB | GroupC GroupD | GroupE GroupF
1 1010 2016 | 25 32 | 10 15 |
2 1011 2016 | 25 20 | 10 10 |
3 1012 2016 | – – | – – | 10 40
4 1011 2017 | 10 15 | 42 31 |
Can you please some one help to me create the result query for this sample
Reply