title: How to make two pivot column with same column name using SQL Server?
body: <p>I wants to make two pivot column using aggregate function with same column.</p>
  
  <pre><code>ItemLookupCode    StoreID    DepartmentID    Weeks        QtySold   AsOfWeekOnHand
  ----------------------------------------------------------------------------------
  610759C2000        1001        23             30             0         1.5
  610759C2000        1001        23             31             0          0
  610759C2000        1004        23             30             0          2
  610759C2000        1004        23             31             0         3.5 
  610759C2000        1201        23             30           0.6395       1
  610759C2000        1201        23             31           0.6395       2
  </code></pre>
  
  <p>I tried using the following query. But it's wrong. What is the correct way?</p>
  
  <pre><code>select itemlookupcode, storeid, departmentid,[30],[31] from 
  (
      select 
          fr.itemlookupcode,
          fr.storeid,
          fr.departmentid,
          fr.asofweekonhand,
          fr.weeks,
          fr.QtySold
      from 
          #finalresult fr
  ) x
  pivot 
  (
      sum(QtySold)
      for weeks in ([30],[31])
  ) p1 
  pivot 
  (
      sum(asofweekonhand)
      for weeks in ([30],[31])
  ) p2 
  </code></pre>
  
  <p><strong>Note</strong></p>
  
  <blockquote>
    <p>Can we specify the column name as</p>
  </blockquote>
  
  <pre><code>Week30Sold    Week31Sold    Week30AsOfWeekOnHand    Week31AsOfWeekOnHand
  -------------------------------------------------------------------------
  </code></pre>