title: Is it possible to have multiple pivots using the same pivot column using SQL Server
body: <p>I am facing the following challenge. I need to rotate table data twice over the same column.
  Here's a screenshot of the data.</p>
  
  <p><img src="https://i.stack.imgur.com/RVEqO.png" alt="A list of items with a purchasing and a selling value for each year the items were sold"></p>
  
  <p>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):</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The result is not exactly what I was hoping for (see image below):</p>
  
  <p><img src="https://i.stack.imgur.com/4j9ti.png" alt="Actual situation: Too many rows"></p>
  
  <p>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?</p>
  
  <p><img src="https://i.stack.imgur.com/AaRqv.png" alt="Desired situation: One row for each item ID"></p>