Getting invalid column name using a pivot table

Question

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'.

I hate it when it's so simple I can't see it. That was it. Post as answer so I can give you props. — Michael Robinson
– Michael Robinson, Commented Nov 22, 2013 at 21:31

Hamlet Hakobyan · Accepted Answer · 2013-11-22 21:36:58Z

5

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.

answered Nov 22, 2013 at 21:36

Hamlet Hakobyan

33.5k6 gold badges55 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

tecshack · Accepted Answer · 2013-11-22 21:38:18Z

Try this:

Select FirstName
    , LastName
    , [week44], [week45], [week46], [week47], [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

The deal is that the [Hours] data is now in the [weekXX] column and is no longer a data element available for retrieval.

dudeNumber4 · Accepted Answer · 2013-11-22 21:27:14Z

0

I believe you just need to wrap keywords [Hours], [Date], [Weekday].

answered Nov 22, 2013 at 21:27

dudeNumber4

4,8679 gold badges44 silver badges59 bronze badges

1 Comment

Michael Robinson Over a year ago

Nope, that didn't do it.

Collectives™ on Stack Overflow

Getting invalid column name using a pivot table

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related