user3165474 user3165474 - 2 months ago 10
SQL Question

SQL :Varchar invalid for Sum operator

I use this code trying to get sum of 4,202 & 203 but it return me error

SELECT *
FROM
( SELECT id,fieldNumber,fieldData FROM iso_fields) AS SourceTable
PIVOT
(
SUM(fieldData)
FOR fieldNumber IN ([4],[202],[203])
) AS PivotTable;


Error :

Operand data type varchar is invalid for sum operator.


Reason:

Table using varchar instead of int


What can i do about it? There is no way to alter the table back.

Answer

Datatype of fieldData is varchar so the error. If fieldData column does not have any varchar data then you can do an explicit conversion to INT in pivot source query.

SELECT *
FROM
( SELECT id,fieldNumber, fieldData FROM iso_fields) AS SourceTable
PIVOT
(
 MAX(fieldData) 
FOR fieldNumber IN ([4],[202],[203])
) AS PivotTable;