Unpivot table and calculate field

Question

I have a bad schema and need to unpivot some fields and also calculate a date. Here's what I have:

DATE1       DATE7       H1  H2  H3  H4  H5  H6  H7
2011-09-19  2011-09-25  3   5   2   7   8   0   0
2011-09-26  2011-10-02  8   6   4   2   1   0   0
2011-10-03  2011-10-09  7   9   5   3   6   0   0

H1 is the number of hours spent on Monday. H2 is for Tuesday, and so forth. H7 is Sunday.

Date1 contains the full date on Monday and Date7 is for Sunday. This is a problem, because I need one date and one amount of hour per row.

This is what I need:

DATE        HOUR
2011-09-19  3
2011-09-20  5
2011-09-21  2
2011-09-22  7
2011-09-23  8
2011-09-24  0
2011-09-25  0
2011-09-26  8
2011-09-27  6
2011-09-28  4
2011-09-29  2
2011-09-30  1
....

With UNPIVOT, unpivoting H1 to H7 worked, but I couldn't figure out how to solve the DATE field.

Here's the SQLFiddle to this case: http://sqlfiddle.com/#!3/4e012/2/0

Thanks in advace!

Answer 1

A little string extraction from the column names do the trick:

SELECT      DATEADD(DAY, CAST(RIGHT([WeekDay],1) AS int) - 1, Date1) As [Date],
            [Hour]
FROM        MyTable
UNPIVOT     (
                [Hour] FOR [WeekDay] IN (H1,H2,H3,H4,H5,H6,H7)
            ) upvt