user3590485 user3590485 - 7 months ago 9
SQL Question

How to use SUM in pivot table(CAST cannot be used in aggregate function)

I am using the following pivot query:

select UserId_Fk,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday
from CheckInCheckOut

PIVOT
(

SUM(CAST(CountHours as decimal(18,2)))
FOR Day
IN([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday])

)
as PivotTable

Table[CheckInCheckOut]

CheckInCheckOutId int
UserId_Fk int
CountHours nvarchar(50)
Day nvarchar(50)


There is something wrong how I am using SUM here. I tried

select SUM(CAST(CountHours as decimal(18,2))) from CheckInCheckOut


which gives me the correct result, but the same I am not able to use in Pivot table.

Answer

try cast your data before make a pivot query

  select UserId_Fk,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday
      from    (Select UserId_Fk,CAST(CountHours as decimal(18,2))as CountHours,[Day]
   from CheckInCheckOut) as convertedtable

    PIVOT
    (

     SUM(CountHours) 
     FOR Day
     IN([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday])

    )
   as PivotTable
Comments