title: How to make pivot table in SQL Server in my case?
body: <p>I wants to display the record by same column. I don't know how describe the question also.</p>
  
  <p>I have a table called <code>SoldQtyTable</code></p>
  
  <pre><code>ItemNo    Weeks    Years    QtySold    AsOfWeekOnHand
  ----------------------------------------------------
  1           1        2017      5         3
  2           1        2017      2         5
  3           1        2017      66        70
  1           2        2017      4         33
  </code></pre>
  
  <p>I wants to display like below</p>
  
  <pre><code>ItemNo    Years    [1QtySold]    [1_OnHand]    [2QtySold]    [2_OnHand]
  -----------------------------------------------------------------------
  1          2017       5               3            4            33
  2          2017       2               5
  3          2017      66               70
  </code></pre>
  
  <p>I tried in this way. But It doesn't work</p>
  
  <pre><code>select 
      PVT1.ItemID,
      PVT1.StoreID,
      PVT1.Years,
      isnull([1],0) as [1QtySold], isnull([2],0) as [2QtySold],
      isnull([1_OnHand],0) as [1_OnHand], isnull([2_OnHand],0) as [2_OnHand]
  from 
  (
      SELECT
          ItemID,
          StoreID,
          Years,
          Weeks,
          AsOfWeekOnHand
      FROM
          SoldQtyTable
  ) L
  PIVOT
  (
      SUM(AsOfWeekOnHand) 
      FOR Weeks IN ( [1_OnHand], [2_OnHand])
  ) AS PVT1
  LEFT JOIN
  (
      SELECT
          ItemID,
          StoreID,
          Years,
          Weeks,
          QtySold
      FROM
          SoldQtyTable
  ) L
  PIVOT
  (
      SUM(QtySold) 
      FOR Weeks IN ( [1soldQty], [2soldQty]
  ) AS PVT2 on PVT2.ItemID = PVT1.ItemID and PVT1.Years = PVT2.Years
  where 
      PVT1.years = 2017
  </code></pre>