title: TSQL Reporting - Pivot data by day of week?
body: <p>I'm writing a report that needs to display a week's worth of receipts, grouped by locations, with a separate column for each day of the week. This will be delivered via SSRS and SQL Server 2008 R2. End result should look like this: (but for all 7 days of the week)</p>
  
  <pre><code>Location    Monday    Tuesday
  Building3   $100      $75
  Building4   $25       $35
  Building5   $105      $21
  </code></pre>
  
  <p>I wrote the following T-SQL SELECT statement to get this data from my reporting table variable (@reporting). With the intention of then grouping the output by location to get the layout shown above.</p>
  
  <p>The raw data lists the receipt date, total, and location in a single row. I want to pivot the data so that day of week is on top. There has got to be a better way to do it than you see below. Any help? </p>
  
  <pre><code>    SELECT
          LocationKey.Location
          ,Sunday.Cost as Sunday
          ,Monday.Cost as Monday
      FROM 
  
          (
              SELECT DISTINCT Location 
              FROM @reporting
              WHERE Location NOT IN ('Building01', 'Building02', '')
          ) AS LocationKey
  
      LEFT JOIN  
  
  (SELECT  
  
  sunday.Location
  ,sunday.Cost
  
  FROM @reporting as sunday (nolock) 
  
  WHERE DATEPART(weekday,sunday.ReceiptDate)= 1
  
          ) AS Sunday
          ON Sunday.Location = LocationKey.Location
  
  LEFT JOIN  
  
  (SELECT  
  Monday.Location,
  Monday.Cost
  
  FROM @reporting as Monday (nolock) 
  
  WHERE DATEPART(weekday,Monday.ReceiptDate)= 2
  
          ) AS Monday
          ON Monday.Location = LocationKey.Location
  </code></pre>