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

Question

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

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

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?

Nadbus

Exactly the question I needed -- was going to ask something similar myself! – Tien

I think on the top of your sql query if you group by item ID and aggregate P2000, P2001, P2002, P2003, S2000, S2001, S2002, S2003 using sum( ), It will fetch you the desired results. It worked for me. – Smriti

Answer 1

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

Lowpressure

Thanks for the unpivot suggestion. That hadn't crossed my mind (and not sure if it would have in the future :-) ) – Nadbus

@RobVermeulen Yeah the unpivot function works great. I just updated my answer with a version that uses cross apply as well. The both will get the same result. – Lowpressure

Would "cross apply" be faster? – Nadbus

@RobVermeulen That is hard to say, it really depends on many factors. If you have concerns about speed, then you should try different variations of the query to determine what will work best for your situation. – Lowpressure

i know thank you posts are unwelcome, but i just had to tell you a great thank you! you have saved my week sir. before reaching this post the only solution i found was to pivot twice on the same table and then join them, but for some reason the second pivot was running on the cartesian product of the join rather than on the table itself, so as soon as the number of lines exceeded 1000 the execeution time rocketed sky high. so it's only after knowing of the existence of unpivot (thanks to you) that i was able to solve my problem. cheers! – Gambill

As I look at this answer for my own use, would I be correct in saying that this method could only be used as long as the aggregation function you plan on applying to both types of values are the same? Lets say you wanted to SUM the Purchase value, but you wanted to AVG the Selling value. I imagine the best answer would be to stick with OP's code and use the logic that @pieterGeerkens suggests? – Guatemala

@Guatemala if you need to use different types of aggregation , then you'd want to use a case expression along with the aggregate function similar to JamieD77's answer – Lowpressure

Another time you can't use this is when the two columns you want to pivot out are different types :(. If you follow this path, you end up with an error like The type of column "some_float_column" conflicts with the type of other columns specified in the UNPIVOT list. – Thisbe

@Thisbe if you use the CROSS APPLY version then you can cast/convert the values to the same data types. – Lowpressure

Answer 2

One easy way to pivot multiple columns is to just use Aggregate(Case) expressions.

SELECT  [Item ID],
        [P2000] = SUM(CASE WHEN [Year] = 2000 THEN [Purchasing value] END),
        [P2001] = SUM(CASE WHEN [Year] = 2001 THEN [Purchasing value] END),
        [P2002] = SUM(CASE WHEN [Year] = 2002 THEN [Purchasing value] END),
        [P2003] = SUM(CASE WHEN [Year] = 2003 THEN [Purchasing value] END),
        [S2000] = SUM(CASE WHEN [Year] = 2000 THEN [Selling value] END),
        [S2001] = SUM(CASE WHEN [Year] = 2001 THEN [Selling value] END),
        [S2002] = SUM(CASE WHEN [Year] = 2002 THEN [Selling value] END),
        [S2003] = SUM(CASE WHEN [Year] = 2003 THEN [Selling value] END)
FROM    ItemPrices
GROUP BY [Item ID]

Choreography

Answer 3

Use a GROUP BY ItemID, with aggregate function SUM(isnull(value,0)) on each of the results columns.

Gasparo

Answer 4

I achieved something similar by pivoting the already pivoted dataset (essentially pivoting twice), then grouping by the necessary attributes to get the results Im after. Still executes in a couple of seconds with thousands of rows.' Using the example in the question, the pivoting twice is correct, there just needs to be a summing of the buy and sell values and a group by at the end of the statement.

SELECT [Item ID], 
        sum([P2000]),sum([P2001]),sum([P2002]),sum([P2003]),
        sum([S2000]),sum([S2001]),sum([S2002]),sum([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

group by Item_ID

Samaveda

Answer 5

Agreed with the above answer about pivot and unpivot. Below is a complete working example in Snowflake, just for reference, even thought the ask was for SQL Server.

drop table if exists sql_start_pivot_double;

create table sql_start_pivot_double
( category  varchar(100)
, month_01  varchar(100)
, month_02  varchar(100)
, month_03  varchar(100)
)
;

insert into sql_start_pivot_double
select column1, column2, column3, column4
  from values
       ('A', 3, 6, 2)
     , ('B', 5, 3, 4)
     , ('B', 8, 2, 9)
     , ('C', 3, 9, 5)
     , ('D', 1, 2, 7)
;

select * from sql_start_pivot_double;

with months_as_columns as (
    select category, sum(month_01) month_01, sum(month_02) month_02, sum(month_03) month_03
      from sql_start_pivot_double
     group by category
),
months_as_rows as (
    select category, month, total
      from months_as_columns
   unpivot (total for month in (month_01, month_02, month_03))
)
select *
  from months_as_rows
 pivot (sum(total) for category in ('A', 'B', 'C', 'D'))
    as t (month, a, b, c, d)
;

Output is:

+----------+----------+----------+----------+
| CATEGORY | MONTH_01 | MONTH_02 | MONTH_03 |
|----------+----------+----------+----------|
| A        | 3        | 6        | 2        |
| B        | 5        | 3        | 4        |
| B        | 8        | 2        | 9        |
| C        | 3        | 9        | 5        |
| D        | 1        | 2        | 7        |
+----------+----------+----------+----------+
+----------+---+----+---+---+
| MONTH    | A |  B | C | D |
|----------+---+----+---+---|
| MONTH_01 | 3 | 13 | 3 | 1 |
| MONTH_02 | 6 |  5 | 9 | 2 |
| MONTH_03 | 2 | 13 | 5 | 7 |
+----------+---+----+---+---+

Rock on!

Plasticize