Using Pivot in SQL Server to Display dates as days of the week
I have table that has a column of type DateTime,
I would like to pivot on the datetime column so that the dates are displayed as columns (as days of the week Monday, Tuesday etc).
So for example I have a table like this (can't remember how SQL Server displays full datetimes but you get the idea):
BugNo | DateOpened | TimeSpent
1234 | 16/08/2010 | 1.0
4321 开发者_如何学编程 | 16/08/2010 | 3.5
9876 | 17/08/2010 | 1.5
6789 | 18/08/2010 | 7.0
6789 | 19/08/2010 | 6.5
6789 | 20/08/2010 | 2.5
I would like to pivot on the DateOpened
column to create a result set like this
|TimeSpentOnBugByDay| Mon | Tue | Wed | Thu | Fri | Sat | Sun
1234 1
4321 3.5
9876 1.5
6789 7.0
6789 6.5
6789 2.5
I should point out that I'll only be retrieving one week at a time.
I'm not sure if this is possible, though I'm pretty certain I have seen something like this before (that I didn't write).
You can do this
SELECT BugNo, [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday]
FROM (
SELECT BugNo, DATENAME(dw, DateOpened) AS DayWeek, TimeSpent
FROM Bugs
) AS src
pivot (
SUM(TimeSpent) FOR DayWeek IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])
) AS pvt
精彩评论