T-SQL Pivot to display One Comment field per week

Question

I've been asked to change a pivot query that currently displays a week ending date, category and hours by date for one week in a row. I've been asked to display a comment field at the end of the row and I can't figure out how to alter the query to do this.

The table is structured like this

Category   Date      Comments     Hours
test       8/2/2010  myComment      2
test       8/3/2010                 8
test       8/4/2010                 4
test       8/5/2010                 3
test       8/6/2010                 5

I would like the data to display like this. I have a query that will diplay all of this except the comment. On the front End I'm only going to allow one comment per week and add it to the Monday date row in the table for each week/category combination.

WeekEnding Category  SunHrs MonHrs TuesHrs WedHrs ThuHrs FriHrs SatHrs  Comment
8/7/2010   test      0      1      1     1      1      1      1       myComment

Here is the query before adding the comment field which works fine.

DECLARE @WeekEnding datetime
DECLARE @UserName nvarchar(245)

SET @WeekEnding = '09/04/2010'
SET @UserName = 'brogers'


SELECT
   @WeekEnding      WeekEnding

  ,CategoryID
  ,isnull([1], 0)  SunHrs
  ,isnull([2], 0)  MonHrs
  ,isnull([3], 0)  TueHrs
  ,isnull([4], 0)  WedHrs
  ,isnull([5], 0)  ThuHrs
  ,isnull([6], 0)  FriHrs
  ,isnull([7], 0)  SatHrs
 from (select  CategoryID, Datepart(dw, TimeEntryDate) DOW, TimeEntryDuration Hours
        from dbo.aspnet_starterkits_TimeEntry
        where TimeEntryDate between dateadd(dd,  -6, @WeekEnding) and @WeekEnding) Source
  pivot (max(Hours) for DOW in ([1],[2],[3],[4],[5],[6],[7]) ) as pvt

I'm not sure how to add the comment field to the end of the row. When I do add it I get a result like this

WeekEnding Category  SunHrs MonHrs TuesHrs WedHrs ThuHrs FriHrs SatHrs  Comment
8/7/2010   test      0      0      1       1      1      1      0       

8/7/2010   test      0      1      0       0      0      0      0       myComment

I only want one row per weekending/category combination and one comment per row in the output. Here is the query that where I added the comment field and displays incorrectly.

Can anyone point out how to display one comment per week/categroy row?

DECLARE @WeekEnding datetime
DECLARE @UserName nvarchar(245)

SET @WeekEnding = '09/04/2010'
SET @UserName = 'brogers'


SELECT
   @WeekEnding      WeekEnding
  ,TimeEntryDescription 
  ,CategoryID
  ,isnull([1], 0)  SunHrs
  ,isnull([2], 0)  MonHrs
  ,isnull([3], 0)  TueHrs
  ,isnull([4], 0)  WedHrs
  ,isnull([5], 0)  ThuHrs
  ,isnull([6], 0)  FriHrs
  ,isnull([7], 0)  SatHrs
 from (select  
        CategoryID, 
        Datepart(dw, TimeEntryDate) DOW, 
        TimeEntryDuration Hours, 
        TimeEntryDescription
        from dbo.aspnet_starterkits_TimeEntry
        where TimeEntryDate between dateadd(dd,  -6, @WeekEnding) and @WeekEnding) Source
  pivot (max(Hours) for DOW in ([1],[2],[3],[4],[5],[6],[7]) ) as pvt

Even though the source table has a comment field for every day of the week, I only want one comment per week and one row per week/category combination.

I will restrict the input to only allow one per week (monday for example) and want this one comment to display at the end of the row on the output query.

Answer 1

Join the pivot table with the source table on date and retrieve min(comment).

DECLARE @WeekEnding datetime
DECLARE @UserName nvarchar(245)

SET @WeekEnding = '09/04/2010'
SET @UserName = 'brogers'

;with Report as (
SELECT
   @WeekEnding      WeekEnding
  ,TimeEntryDescription 
  ,CategoryID
  ,isnull([1], 0)  SunHrs
  ,isnull([2], 0)  MonHrs
  ,isnull([3], 0)  TueHrs
  ,isnull([4], 0)  WedHrs
  ,isnull([5], 0)  ThuHrs
  ,isnull([6], 0)  FriHrs
  ,isnull([7], 0)  SatHrs
 from (select  
        CategoryID, 
        Datepart(dw, TimeEntryDate) DOW, 
        TimeEntryDuration Hours, 
        TimeEntryDescription
        from dbo.aspnet_starterkits_TimeEntry
        where TimeEntryDate between dateadd(dd,  -6, @WeekEnding) and @WeekEnding) Source
  pivot (max(Hours) for DOW in ([1],[2],[3],[4],[5],[6],[7]) ) as pvt
)
select * 
,(select min(comment) from dbo.aspnet_starterkits_TimeEntry te where TimeEntryDate between dateadd(dd,  -6, @WeekEnding) and @WeekEnding)) [Comment]
from Report r

Answer 2

This is probably easier with an old style pivot

;With [Source] As
(
select  
        CategoryID, 
        Datepart(dw, TimeEntryDate) DOW, 
        TimeEntryDuration Hours, 
        TimeEntryDescription
        from dbo.aspnet_starterkits_TimeEntry
        where TimeEntryDate between dateadd(dd,  -6, @WeekEnding) and @WeekEnding
)
SELECT
   @WeekEnding      WeekEnding
  ,TimeEntryDescription 
  ,CategoryID
  ,max(case when DOW = 1 then [Hours] else 0 end)  SunHrs
  ,max(case when DOW = 2 then [Hours] else 0 end)  MonHrs
  ,max(case when DOW = 3 then [Hours] else 0 end)  TueHrs
  ,max(case when DOW = 4 then [Hours] else 0 end)  WedHrs
  ,max(case when DOW = 5 then [Hours] else 0 end)  ThuHrs
  ,max(case when DOW = 6 then [Hours] else 0 end)  FriHrs
  ,max(case when DOW = 7 then [Hours] else 0 end)  SatHrs
  ,max(comment) as comment
 from [Source]
 group by 
   TimeEntryDescription 
  ,CategoryID