TSQL Reporting - Pivot data by day of week?

Question

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)

Location    Monday    Tuesday
Building3   $100      $75
Building4   $25       $35
Building5   $105      $21

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.

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?

    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

Answer 1

Probably the easiest way to do this is to PIVOT on the DATENAME.

SELECT location, 
       [Saturday], 
       [Sunday], 
       [Monday], 
       [Tuesday], 
       [Wednesday], 
       [Thursday], 
       [Friday] 
FROM   (SELECT COST, 
               location, 
               Datename(weekday, receiptdate) DAY 
        FROM   @reporting 
        WHERE  location NOT IN ( 'Building01', 'Building02', '' )) p 
      PIVOT ( 
        SUM (COST) 
        FOR DAY IN ( [Saturday], [Sunday], [Monday], [Tuesday], [Wednesday], 
       [Thursday], [Friday]) ) pvt 

See it working at this data.se query

Another way is to use several self joins but not do subqueries. The key here is the Join Clause

SELECT LocationKey.Location,
       SUM(Sunday.Cost) As [Sunday],
       SUM(Monday.Cost) As [Monday],
       SUM(Tuesday.Cost) As [Tuesday],
       SUM(Wednesday.Cost) As [Wednesday],
       SUM(Thursday.Cost) As [Thursday],
       SUM(Friday.Cost) As [Friday],
       SUM(Saturday.Cost) As [Saturday]
FROM
      (SELECT DISTINCT Location 
            FROM @reporting
            WHERE Location NOT IN ('Building01', 'Building02', '')) LocationKey
      LEFT JOIN @Reporting Sunday 
      ON LocationKey.Location = Sunday.Location
         AND DATEPART(weekday,sunday.ReceiptDate)= 1
      LEFT JOIN @Reporting Monday
      ON LocationKey.Location = Monday.Location
         AND DATEPART(weekday,Monday.ReceiptDate)= 2
      LEFT JOIN @Reporting Tuesday
      ON LocationKey.Location = Tuesday.Location
         AND DATEPART(weekday,Tuesday.ReceiptDate)= 3
      LEFT JOIN @Reporting Wednesday
      ON LocationKey.Location = Wednesday.Location
         AND DATEPART(weekday,Wednesday.ReceiptDate)= 4
      LEFT JOIN @Reporting Thursday
      ON LocationKey.Location = Thursday.Location
         AND DATEPART(weekday,Thursday.ReceiptDate)= 5
      LEFT JOIN @Reporting Friday
      ON LocationKey.Location = Friday.Location
         AND DATEPART(weekday,Friday.ReceiptDate)= 6         
      LEFT JOIN @Reporting Saturday
      ON LocationKey.Location = Saturday.Location
         AND DATEPART(weekday,Saturday.ReceiptDate)= 7

You should note that you should either call SET DATEFIRST or use an offset of @@DATEFIRST to protect your query from the potential of the default settings changing and breaking your query when you use DATEPART(weekday..