Pivot the data into weeks

You could create a permanent table with the weeks in it, and then just use the final query.

If you have a lot of data in the table, and an index on date_worked, and you will only process some of the rows from the data table, it would probably be worthwhile to add a WHERE clause to check date_worked for the range of dates to be processed.  Let me know if that applies here.

If possible at all, I don't want to create temp table.

CORRECTIONS:
*) typo, misspelled "week" as "weak" on the WHILE
*) I used SMALLDATETIME instead of DATETIME
*) I hard-coded "2005" instead of using the current year


DECLARE @weeks TABLE (weekNum TINYINT, weekStart DATETIME, weekEnd DATETIME)
DECLARE @weekNum TINYINT
DECLARE @weekStart DATETIME
DECLARE @weekEnd DATETIME
SET @weekNum = 1
-- set first week start datetime to Jan 1 of current year
SET @weekStart = STUFF(CONVERT(CHAR(8), GETDATE(), 112), 5, 4, '0101')
SET @weekEnd = DATEADD(MILLISECOND, -3, DATEADD(DAY, 7, @weekStart))

SET NOCOUNT ON
WHILE @weekStart <= GETDATE()
BEGIN
      INSERT INTO @weeks VALUES(@weekNum, @weekStart, @weekEnd)
      SET @weekNum = @weekNum + 1
      SET @weekStart = DATEADD(DAY, 7, @weekStart)
      SET @weekEnd = DATEADD(DAY, 7, @weekEnd)
END --WHILE
SET NOCOUNT OFF

--SELECT * FROM @weeks

SELECT [key],
      SUM(CASE WHEN weekNum = 1 THEN hours ELSE 0 END) AS [Week 1],
      SUM(CASE WHEN weekNum = 2 THEN hours ELSE 0 END) AS [Week 2],
      SUM(CASE WHEN weekNum = 3 THEN hours ELSE 0 END) AS [Week 3],
      SUM(CASE WHEN weekNum = 4 THEN hours ELSE 0 END) AS [Week 4]
FROM data
INNER JOIN @weeks ON data.date_worked BETWEEN weekStart AND weekEnd
GROUP BY [key]

>> If possible at all, I don't want to create temp table. <<

I understand.  But SQL is actually more efficient joining to that table than using another method.  Also, the table will never be more than 53 rows, so it will always fit in one data page.  

Finally, you could create a permanent table with the current year's weeks already in it and just issue the final SELECT shown.

>>>>But SQL is actually more efficient joining to that table than using another method.  

sounds persuasive.  true.

but this section of query:

SELECT [key],
     SUM(CASE WHEN weekNum = 1 THEN hours ELSE 0 END) AS [Week 1],
     SUM(CASE WHEN weekNum = 2 THEN hours ELSE 0 END) AS [Week 2],
     SUM(CASE WHEN weekNum = 3 THEN hours ELSE 0 END) AS [Week 3],
     SUM(CASE WHEN weekNum = 4 THEN hours ELSE 0 END) AS [Week 4]
FROM data
INNER JOIN @weeks ON data.date_worked BETWEEN weekStart AND weekEnd
GROUP BY [key]



Do I have to hardcoded weeknum=1, 2, 3, 4? Shouldn't that be dynamic? I.e., what if you have 5 weeks between the dates?

Ah, yes, good point, but you will need to specify at least the starting weekNum / date and, if you want to report on a variable number of weeks, then ending date as well.

Note that there is no way to change the column headings without using dynamic SQL.  So "Week 1" will be the first week *reported* on, "Week 2" will be the second week *reported*, and so on.

For the simplest case, specify starting week (default = 1) and have a fixed 4 weeks reported:

DECLARE @startWeekNum TINYINT
SET @startWeekNum = 3  --<<-- of course could be input param in sp with def. = 1

...other declares...
...build date table, loading only needed week(s) based on @startWeekNum...

SELECT [key],
     SUM(CASE WHEN weekNum = @startWeekNum THEN hours ELSE 0 END) AS [Week 1],
     SUM(CASE WHEN weekNum = @startWeekNum + 1 THEN hours ELSE 0 END) AS [Week 2],
     SUM(CASE WHEN weekNum = @startWeekNum + 2 THEN hours ELSE 0 END) AS [Week 3],
     SUM(CASE WHEN weekNum = @startWeekNum + 3 THEN hours ELSE 0 END) AS [Week 4]
FROM data
INNER JOIN @weeks ON data.date_worked BETWEEN weekStart AND weekEnd
GROUP BY [key]

I don't normally recommend using dynamic SQL, but it would also allow you to avoid a temp/perm table in this particular case and have custom column headings.  As long as you understand and can accept the security implications, it's a good choice here.  Give me a few minutes and I'll post a sample.

Scott, I think you are pretty close to the solution.

Another wrinkle here, is that given two dates, starting date, and ending date, how do you dynamically break the days in between into weeks, and generate a report columnized by week number on the fly?

an example is due here;

say January 1, 2005 to January 31, 2005 is the input of this store procedure.

I want stored procedure to generate report of total hours by week number horizontally by user provided two dates.
Hope I made this clear enough for you to work

OK, look at the results of this; if you like it, comment the PRINT @sql and uncomment the EXEC(@sql) and this should be a good start.  
You should add some date checks/verifications (start date is actual start of week, or adjust it to one; end date is actual end of week, or adjust it one; start date is < current date; etc.); if you want some code for that as well, let me know.
You could make other enhancements as well.  For example, allow a specific key to be passed in; if so, report on that key only, else report on all keys.


Sample invocations:

EXEC workHoursByKey
EXEC workHoursByKey '2005-01-08', '2005-03-15'


CREATE PROCEDURE WorkHoursByKey
      @startDate DATETIME = NULL,  --default = Jan 1 of current year
      @endDate DATETIME = NULL    --default = four wks listed
AS
IF @startDate IS NULL
      SET @startDate = STUFF(CONVERT(CHAR(8), GETDATE(), 112), 5, 4, '0101')
IF @endDate IS NULL
      SET @endDate = DATEADD(MILLISECOND, -3, DATEADD(DAY, 28, @startDate))

DECLARE @workDate DATETIME
DECLARE @sql VARCHAR(3000)

SET @workDate = @startDate

SET @sql = 'SELECT [key], ' + CHAR(13)

WHILE @workDate < @endDate
BEGIN
      SET @sql = @sql + '    SUM(CASE WHEN date_worked BETWEEN ''' +
            CONVERT(VARCHAR(30), @workDate, 121) + ''' AND ''' +
            CONVERT(VARCHAR(30), DATEADD(MS, -3, DATEADD(DAY, 7, @workDate)), 121) +
            ''' THEN hours ELSE 0 END) AS [Week of ' +
            CONVERT(CHAR(5), @workDate, 1) + '],' + CHAR(13)
      SET @workDate = DATEADD(DAY, 7, @workDate)
END --WHILE

-- remove trailing comma, then complete building query
SET @sql = STUFF(@sql, LEN(@sql) - 1, 2, '') + CHAR(13) +
      'FROM data ' + CHAR(13) +
      'WHERE date_worked BETWEEN ''' + CONVERT(VARCHAR(30), @startDate, 121) +
      ''' AND ''' + CONVERT(VARCHAR(30), @endDate, 121) + '''' + CHAR(13) +
      'GROUP BY [key] '

PRINT @sql
--EXEC(@sql)
GO