Where to do the CASTING in SQL PIVOT Expression

Question

I am using the below SQL query and use pivoting to interchange rows and cols in the SELECT result.

Operand data type varchar is invalid for sum operator.

(Value) col -> DataType-> VARCHAR(256) -> PartyRelationshipSetting Table

I am unable change the datatype in the table because it's a multivalued column and I need to convert the value into INT or relevant datatype to perform the SUM operation used in the below PIVOT expression.

SELECT PartyRelationshipID,[2] AS OrderGroup,[3] AS TaxStatus,[4] AS Area
FROM (SELECT PartyRelationshipID,PartyRelationshipSettingTypeID,Value FROM [Party].PartyRelationshipSetting prs) AS SourceTable
INNER JOIN Party.PartyRelationship prship ON SourceTable.PartyRelationshipID = prship.ID
PIVOT
(
SUM(Value)
FOR PartyRelationshipSettingTypeID IN ([2],[3],[4])
)AS PivotTable

I tried the below methods. But it gives me a syntax error.

1)

CAST(Value FROM [Party].PartyRelationshipSetting prs)AS INT) AS SourceTable

2)

PIVOT
(
SUM(CAST(Value AS INT))
FOR PartyRelationshipSettingTypeID IN ([2],[3],[4])
)AS PivotTable

How to cast this properly.

Pரதீப் Pரதீப் · Accepted Answer · 2018-01-22T03:53:12

Do the conversion in the inner query itself

SELECT PartyRelationshipID,
       [2] AS OrderGroup,
       [3] AS TaxStatus,
       [4] AS Area
FROM   (SELECT PartyRelationshipID,
               PartyRelationshipSettingTypeID,
               Value = Cast(Value AS INT) --here
        FROM   [Party].PartyRelationshipSetting prs) AS SourceTable
       INNER JOIN Party.PartyRelationship prship
               ON SourceTable.PartyRelationshipID = prship.ID
PIVOT(SUM(Value) FOR PartyRelationshipSettingTypeID IN ([2],[3],[4]))AS PivotTable

Where to do the CASTING in SQL PIVOT Expression

1 Answers