SQL SERVER – PIVOT and UNPIVOT Table Examples

I previously wrote two articles about PIVOT and UNPIVOT tables. I really enjoyed writing about them as it was interesting concept. One of the Jr. DBA at my organization asked me following question.

“If we PIVOT any table and UNPIVOT that table do we get our original table?”

I really think this is 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 original data back.

Solarwinds

Let me explain this issue demonstrating simple example.
USE AdventureWorks
GO
-- Creating Test Table
CREATE TABLE Product(Cust VARCHAR(25), Product VARCHAR(20), QTY INT)
GO
-- Inserting Data into Table
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',2)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','SODA',6)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','MILK',1)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','BEER',12)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','MILK',3)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','BEER',24)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',3)
GO
-- Selecting and checking entires in table
SELECT *
FROM Product
GO
-- Pivot Table ordered by PRODUCT
SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT
(SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
ORDER BY PRODUCT
GO
-- Pivot Table ordered by CUST
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT
(SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt
ORDER BY CUST
GO
-- Unpivot Table ordered by CUST
SELECT CUST, PRODUCT, QTY
FROM
(
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT
( SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt) p
UNPIVOT
(QTY FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)
)
AS Unpvt
GO
-- Clean up database
DROP TABLE Product
GO

ResultSet:
-- Selecting and checking entires in table
Cust Product QTY
------------------------- -------------------- -----------
KATE VEG 2
KATE SODA 6
KATE MILK 1
KATE BEER 12
FRED MILK 3
FRED BEER 24
KATE VEG 3

-- Pivot Table ordered by PRODUCT
PRODUCT FRED KATE
-------------------- ----------- -----------
BEER 24 12
MILK 3 1
SODA NULL 6
VEG NULL 5

-- Pivot Table ordered by CUST
CUST VEG SODA MILK BEER CHIPS
------------------------- ----------- ----------- ----------- ----------- -----------
FRED NULL NULL 3 24 NULL
KATE 5 6 1 12 NULL

-- Unpivot Table ordered by CUST
CUST PRODUCT QTY
------------------------- -------- -----------
FRED MILK 3
FRED BEER 24
KATE VEG 5
KATE SODA 6
KATE MILK 1
KATE BEER 12
12


You can see in above example where we are using the SUM aggregated functions. SUM adds up values based on column used in the sum function. In our example Kate and Veg has two entries. In our pivot example with order by Cust the values are summed up. Now when table goes under UNPIVOT operations it transforms the table which is already went under PIVOT operation.

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.

This way we can get the original table back if aggregate functions was not applied on the data or data was in such form that aggregate function might have not made any difference.

Reference : Pinal Dave (https://blog.sqlauthority.com), SQL SERVER – UNPIVOT Table Example, SQL SERVER – PIVOT Table Example

Solarwinds
, , ,
Previous Post
SQL SERVER – Compound Assignment Operators – A Simple Example
Next Post
SQL SERVER – Order of Conditions in WHERE Clause

Related Posts

122 Comments. Leave new

  • Good site..
    My query is…

    i have 3 columns
    item, remarks, remarksby, date
    a, ‘good’, ‘sonu’ , day1
    a, ‘bad’, monu’ , day2
    d, ‘too good’, ‘raju’ , day1
    d, ‘ok’, ‘golu’ , day2

    i have to show like this…

    item , remarks1, remakrsby1, remarks2, remarksby2, remakrs3,remarksby3
    a , good, sonu, bad, monu
    d, too good , raju, ok , golu

    please let me know how to do this … ???
    All thanks in advance

    Reply
    • declare @remarks table (item varchar, remarks varchar(20), remarksby varchar(20) ,dat varchar(20))

      insert into @remarks
      select ‘a’, ‘good’, ‘sonu’ , ‘day1’ union all
      select ‘a’, ‘bad’, ‘monu’ , ‘day2’ union all
      select ‘d’, ‘too good’, ‘raju’ , ‘day1’ union all
      select ‘d’, ‘ok’, ‘golu’, ‘day2’

      –select * from @remarks

      SELECT
      item,
      max(case when id=1 then remarks
      end) as remarks1 ,
      max(case when id=2 then remarks
      end) as remarks2,
      max(case when id=1 then remarksby
      end) as remarksby2,
      max(case when id=2 then remarksby
      end) as remarksby2
      FROM (
      SELECT
      row_number()over (partition by item order by item)id,
      item,
      remarks,
      remarksby
      FROM @remarks
      ) up

      group by
      item

      Reply
  • It was a nice article Dave,Thanks.For those looking for alternative solution below is the solution:

    it can also be done using case statement

    select Product,
    max(case cust when ‘fred’ then Qty end) as ‘FRED’,
    max(case cust when ‘kate’ then Qty end) as ‘KATE’
    from Product
    group by Product

    Reply
  • This helped me most considerably.

    Reply
  • Very much useful this article .

    Reply
  • Very much useful article!.

    Reply
  • Is there any way to make something like pivot without using aggregate functions? I need to see the details, not the sum. How can i do something like a crosstable in SQL Server?

    Reply
  • how can I round an aggregated columns value?

    Reply
  • holas necesito su ayuda kisiera unir dos tablas o mas y que los campos de las tablas no me salgan en forma horizontal , ejemplo :

    select tabla_prueba .id,nombre,apellido,tabla2.id,pieza,grupo from tabla_prueba
    INNER JOIN tabla2 ON tabla_prueba.id=tabla2.id

    este es el resultado de la consulta

    id,nombre,id,nombre
    1 asas 3 ad
    2 zxzx 4 ads

    quisiera ke salgan asi

    id,nombre
    1 asas
    2 zxzx
    id,nombre
    3 ad
    4 ads

    Reply
  • Nice post!

    Reply
  • Nice Article.. for those who have not used pivot before.

    Reply
  • Can we avoid to sort the data in the pivot as pivot sorts the data autmatically
    help please

    Reply
  • I have a requirement in which after Pivot, I don’t want the aggregated value, instead I need ‘Y’ and for Null values it should be ‘N’. How can I achieve this? Please help me.

    Reply
  • What if I have only one column in mytable and i want the values to be displayed horizontally??
    eg. table ‘ONE”
    column
    data
    1
    2
    3
    I want to display as :
    data 1 2 3
    ??

    Reply
  • I need help. I am using sql 2005 and I am trying to do the following query and output to a text fle with fixed-length format in stored procedure once per week:
    Before the printing list can be generated, SQL should select each household (using family number as an indicator) that the member/s of the same household need to have a status of ’40’ in order for that member to be included in the printing list; other valid statuses are ’73’ or ’76’ but not the status of ‘R’ or anything above a status of ’90’. Please note the ‘status’ column is a string type variable. (It is not use in computation).

    Database name = Scores; Table name = BMA; using SQL 2005; in the table-design of BMA, I have the following — (column names, data types, allow nulls). There are two primary keys and cannot contain a ‘null’ value.

    column names data types allow nulls
    (primary key) FamilyNumber varchar(12) no null
    (primary key) FamilyMemNum varchar(8) no null
    Status varchar(3) null
    ActionNeeded varchar(1) null
    SendToPrint varchar(1) null

    Partial row records are as follow:

    FamilyNumber FamilyMemNum Status ActionNeeded SendToPrint
    11011011011A A1345678 40 P P ***This household has 3 members and member# (K3345678)
    11011011011A G2345678 73 P prevented a mailing due to status=’02’ instead of a ’40’ or ’73’
    11011011011A K3345678 02 P or ’76’. SendToPrint is always ‘P’ for the whole family when
    there is at least one family member currently with a ‘P’ in
    ActionNeeded, or a family member who had status=’40’ previously
    & is now a ’73’ or ’76’. This whole family will be included in the
    printing list when the family member (K3345678) status changes to
    a ’40/73/76′.
    22022022022A B2345678 40 P P ***This household only has 1 member, and with a status = ’40’, print
    this.
    33033033033B A1678901 76 P ***This household has 3 members, and all members has status of
    33033033033B B1943250 40 P P either 73, 76 or 40, print this. But only include (B1943250) and
    33033033033B K2583690 40 P P (K2583690) in the print list, since they have ‘P’ for ActionNeeded &
    SendToPrint.
    44044044044G L6521234 40 P P ***This household has 2 members, and both has status =’40’, and ‘P’
    44044044044G B1345678 40 P P (Print) in both ActionNeeded and SendToPrint, therefore print this.
    55055055055R V2134567 91 P P ***This should be excluded from the print list even though it has
    ‘P’ in both ActionNeeded and SendToPrint columns, because the
    family do not have a status of ’40’ or ’73’ or ’76’.
    66066066066E Y7531908 73 P ***This family should be excluded from the print list although both
    66066066066E C1253602 76 P has ‘valid’ print status of ’40/73/76′, but since none of their
    status=’40’, do not include this family (66066066066E) in this print
    list.

    *** Each set or groupings of FamilyNumber is similar to an apartment having ‘The Smith’s’ household as an example (ie: 11011011011A), and FamilyMemNum is similar to each individual children and spouse living in that apartment, each person is identified with a FamilyMemNum (ie: A1345678, G2345678, K3345678), to be included in the printing list of the week, each FamilyMemNum should have a status of either ’40’ or ’73’ or ’76’, otherwise nobody is included on the print list of the week, (ie: K3345678) has a status of ’02’, that disqualifies the whole family (11011011011A) from making to the printing list of the week, until that family member’s status changed to one of the ‘valid’ printing status of ’40’ or ’73’ or ’76’. Once (K3345678) has a valid status, say a ’76’ which makes the whole family ‘valid’ to be included in the printing list, but only individuals with a status of ’40’ in a ‘valid’ family will get a mail. Those with ‘valid’ status of ’73’ and ’76’ need not be included in the mailing list.

    (output to a flat file or text file in fixed-length format from stored procedure, no space or comma separating the two columns in the output file):
    like so — FamilyNumberFamilyMemNum

    Printing list of the week:
    22022022022AB2345678
    33033033033BB1943250
    33033033033BK2583690
    44044044044GL6521234
    44044044044GB1345678

    *** The rest are not included on the print list due to one of the family members do not have a ‘valid’ print status of ’40’ or ’73’ or ’76’ making the whole family ‘invalid’ for the print list even if there is one member who may have a valid status, the invalid family member supercedes the valid family member. Another situation would be the whole family or a part of it has a valid status of ’73’ or ’76’ and these people may have had a status of ’40’ previously, therefore need no further mailing.

    Thank you in advance for helping me solving this difficult SQL query.
    — Larry

    Reply
  • I have a common problem on SQl side. I am providing details and data and also required output as below:
    there are two table
    1) Table1 has data and column:

    PortfolioID |Project | Description | Jan |Feb | March | April |May |Jun|July|Aug|Sept|Oct|Nov|Dec
    1 | P1 | Test1 |-62.5 |-62.5|-62.5|-62.5|-62.5|-62.5|-62.5|-62.5|-62.5|-62.5|-62.5|-62.5
    1|Project|Des|2012-1|2012-2|2012-3|2012-4|2012-5|2012-6|2012-7|2012-8|2012-9|2012-10|2012-11|2012-12

    2) Table 2:

    PortfolioID |Project | Description | Jan |Feb | March | April |May |Jun|July|Aug|Sept|Oct|Nov|Dec
    1 | P1 | Test1 |-6 |-6|-5|2.5|.5|-6|5|-6|-6|-6|-6|-62.5
    1|Project|Des|2012-1|2012-2|2012-3|2012-4|2012-5|2012-6|2012-7|2012-8|2012-9|2012-10|2012-11|2012-12

    Output:
    PortfolioID|Project|Des|Date|Table1Data|Tabl2Data
    1 P1 Test1 2012-1 0 -6
    1 P1 Test1 2012-2 0 -6
    1 P1 Test1 2012-3 0 -6
    1 P1 Test1 2012-4 0 -6
    1 P1 Test1 2012-5 62.5 -6
    1 P1 Test1 2012-6 0 -6
    1 P1 Test1 2012-7 0 -6
    1 P1 Test1 2012-8 0 -6
    1 P1 Test1 2012-9 0 -6
    1 P1 Test1 2012-10 0 -6
    1 P1 Test1 2012-11 0 -6
    1 P1 Test1 2012-12 0 -6

    Please help me and Thanks in advance

    Reply
  • Here’s a different approach using CTE:

    WITH pivotdata as (
    select
    Product,
    cust,
    qty
    from product
    )
    SELECT Product,[FRED],[KATE]
    from pivotdata
    pivot(SUM(qty) for cust in ([FRED],[KATE])) as p;

    Reply
  • I want to create a Pivot Table using a Table Variable. Following is my script which gives error
    Msg 1087, Level 15, State 2, Line 8
    Must declare the table variable “@DateTable”

    This means the table variable is not accessible to Execute statement
    ==============================
    Declare @DateTable Table
    (MasterDate date, DoM int, Mth int, TotalPaxInLocation int)
    Declare @GivenDate date
    Declare @Location nvarchar(6)
    Declare @StartDate Date
    Declare @EndDate Date
    Declare @TotalPax int

    Set @StartDate = ‘2014-01-01’
    Set @EndDate = ‘2014-12-31’
    Set @Location = ‘MADINA’
    Set @GivenDate = @StartDate

    While @GivenDate <= @EndDate
    BEGIN
    Insert into @DateTable
    (MasterDate, Dom, Mth)
    Select @GivenDate, datepart(d,@givendate), datepart(M, @GivenDate)

    Select
    @TotalPax = isnull(sum(dbo.TotalPax(RM.RegID)),0)
    from RegistrationMaster RM
    Where
    Case
    When @GivenDate Between RM.ArrivalDate and RM.DepDate
    Then Case When @GivenDate Between RM.ExpMadinaDate and RM.ExpMadinaReturn then 'MADINA' else 'MAKKAH' end
    else
    Case When @GivenDate < RM.ArrivalDate then 'NOT ARRIVED' else 'DEPARTURE' end
    END = @Location

    Update @DateTable
    Set TotalPaxInLocation = @TotalPax
    Where MasterDate = @GivenDate

    Set @GivenDate = DATEADD(D,1,@GivenDate)
    END

    Select DoM, Mth, TotalPaxInLocation from @DateTable
    order by MasterDate

    DECLARE @PivotColumnHeaders VARCHAR(MAX)
    SELECT @PivotColumnHeaders =
    COALESCE(
    @PivotColumnHeaders + ',[' + cast(A.DoM as varchar) + ']',
    '[' + cast(A.DoM as varchar)+ ']'
    )
    FROM
    (
    select
    distinct DoM
    from @DateTable DT
    ) A

    DECLARE @PivotTableSQL NVARCHAR(MAX)

    SET @PivotTableSQL = '
    SELECT *
    FROM (
    Select * from
    (
    Select
    DoM, Mth, TotalPaxInLocation
    From @DateTable)

    AS PivotData
    PIVOT (
    Sum(TotalPaxInLocation)
    FOR DoM IN (
    ' + @PivotColumnHeaders + ' ) )

    AS PivotTable ) A

    '

    EXECUTE(@PivotTableSQL)

    Reply
    • Could be that you are adding the ‘END’ statement prior to calling the @DateTable after your WHILE loop. Seems like the @DateTable becomes out of scope.

      Reply
    • Could be that your @DateTable is falling out of scope due to that ‘END’ statement for your WHILE loop.

      Reply
  • Hi

    I have database in structure like below

    Newspaper State1 state2 state3 state4
    ABC 1000 5684 5687 246
    DEF 879 2547 2578 2357

    But I need to the structure like below

    How can i change this

    Newspaper State copies
    ABC State1 1000
    ABC State2 5684
    ABC State3 5687
    ABC State4 246
    DEF State1 879
    DEF State2 2547
    DEF State3 2578
    DEF State4 2357

    Pls help me

    Maideen

    Reply
  • table return this type record as per table structure

    VendorId MON TUE WED THU FRI SAT SUN
    ———- ———– ———– ———– ———– ———– ———– ———–
    SPIKE 900 200 500 300 300 100 400

    and i want this type

    VendorId Day IncomeAmount
    ———- ———- ————
    SPIKE MON 900
    SPIKE TUE 200
    SPIKE WED 500
    SPIKE THU 500
    SPIKE FRI 200
    SPIKE SAT 900

    Reply
  • Hello mr. Pinal have some sample log as follows:

    Cod. name description quantity
    ——————————————————
    001 PAPER-A good 50
    002 PAPER-B medium 40
    003 PAPER-C 30
    004 PAPER-D good 50
    :
    :
    and records of the “description” column I want to become columnar but as I do with the vacuum?

    Reply

Leave a Reply

Menu