SQL SERVER – PIVOT Table Example

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

Solarwinds

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

Solarwinds
, , ,
Previous Post
SQL SERVER – 2005 – Twelve Tips For Optimizing Sql Server 2005 Query Performance
Next Post
SQL SERVER – SQL SERVER – UDF – Get the Day of the Week Function – Part 2

Related Posts

79 Comments. Leave new

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

    Reply
  • 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
  • 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
  • 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
  • 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
  • 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
    • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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

Leave a Reply

Menu