Getting invalid column name using a pivot table
sql-server
Solution
You can't use aggregated column as non-pivoted column. You can't have Hours as column in your result set. I believe something wrong in the logic.
Problem
I have this SQL: ``` Select FirstName, LastName, Hours, Case When DatePart(Weekday, Date) = 1 Then Date - 6 Else Date - DatePart(Weekday, Date) + 2 End [WeekOf], IsNull([Week44], 0) as [Week44], IsNull([Week45], 0) as [Week45], IsNull([Week46], 0) as [Week46], IsNull([Week47], 0) as [Week47], IsNull([Week48], 0) as [Week48] from ( Select FirstName, LastName, Hours, Date, 'Week' + Case When DatePart(Weekday, Date) = 1 Then Cast(DatePart(ww, Date - 6) as nvarchar(max)) Else Cast(DatePart(ww, Date - DatePart(Weekday, Date) + 2) as nvarchar(max)) End WeekNo From Entry Where Date between '11/1/2013' and '11/30/2013' ) alpha pivot ( sum(Hours) for WeekNo in ([week44], [week45], [week46], [week47], [week48]) ) p ``` As far as I can see, everything is fine, but I keep getting this error: ``` Msg 207, Level 16, State 1, Line 1 Invalid column name 'Hours'. ```