sqlsql-serverpivotunpivot

Is it possible to have multiple pivots using the same pivot column using SQL Server


I am facing the following challenge. I need to rotate table data twice over the same column. Here's a screenshot of the data.

A list of items with a purchasing and a selling value for each year the items were sold

I want to have one row for each Item ID containing both the purchasing value and the selling value for each year. I tried doing this by selecting the "year" column twice, formatting it a bit so each selling year gets prefixed with a "S" and each purchasing year begins with a "P", and using 2 pivots to rotate around the 2 year columns. Here's the SQL query (used in SQL Server 2008):

SELECT [Item ID], 
        [P2000],[P2001],[P2002],[P2003],
        [S2000],[S2001],[S2002],[S2003]
FROM 
(

SELECT [Item ID]
      ,'P' + [Year] AS YearOfPurchase
      ,'S' + [Year] AS YearOfSelling

  ,[Purchasing value]
  ,[Selling value]
  FROM [ItemPrices]
) AS ALIAS

PIVOT 
(
MIN ([Purchasing value]) FOR [YearOfPurchase] in ([P2000],[P2001],[P2002],[P2003])
)
AS pvt

PIVOT 
(
MIN ([Selling value]) FOR [YearOfSelling] in ([S2000],[S2001],[S2002],[S2003])
)
AS pvt2

The result is not exactly what I was hoping for (see image below):

Actual situation: Too many rows

As you can see, there are still more than one row for each item ID. Is there a way to reduce the number of rows to exactly one per item? So that it looks a bit like the Excel screenshot below?

Desired situation: One row for each item ID


Solution

  • My suggestion would be to apply both the UNPIVOT and the PIVOT functions to get the result.

    The UNPIVOT will turn the PurchasingValue and SellingValue columns into rows. Once this is done, then you can pivot the data into your result.

    The code will be:

    select *
    from
    (
      select itemid, 
        case 
          when col = 'PurchasingValue' then 'P'
          when col = 'SellingValue' then 'S'
        end + cast(year as varchar(4)) new_col,
        value
      from yourtable
      unpivot
      (
        value
        for col in ([PurchasingValue], [SellingValue])
      ) unpiv
    ) src
    pivot
    (
      max(value)
      for new_col in (P2000, P2001, P2002, P2003,
                      S2000, S2001, S2002, S2003)
    ) piv;
    

    See SQL Fiddle with Demo. The result is:

    | ITEMID | P2000 | P2001 | P2002 | P2003 | S2000 | S2001 | S2002 | S2003 |
    --------------------------------------------------------------------------
    |      1 |  1000 |  1100 |  1200 |  1300 |   900 |   990 |  1080 |  1170 |
    |      2 |   500 |   550 |   600 |   650 |   450 |   495 |   540 |   585 |
    

    In SQL Server 2008+ you can use CROSS APPLY with VALUES along with the PIVOT function:

    select *
    from
    (
      select itemid,
        col+cast(year as varchar(4)) new_col,
        value
      from yourtable
      cross apply
      (
        VALUES
            (PurchasingValue, 'P'),
            (SellingValue, 'S')
       ) x (value, col)
    ) src
    pivot
    (
      max(value)
      for new_col in (P2000, P2001, P2002, P2003,
                      S2000, S2001, S2002, S2003)
    ) piv
    

    See SQL Fiddle with Demo