I have a situation where the data type of the column is
VARCHAR(500) and I am casting to
DECIMAL(16,2) for a SUM() but I need to add a CASE statement/expression to alter to a text string if certain conditions are met. I have tried the below syntax, but I get an error of
Msg 8114, Level 16, State 5, Line 7
Error converting data type varchar to numeric.
I can not change DDL - so what change in this query needs to be made in order for the case statement to execute succesfully?
DECLARE @TestData Table ( id int, rep varchar(500), amt varchar(500)) INSERT INTO @TestData (id, rep, amt) VALUES (1, 'John', '200.00'), (2, 'John', '300.00'), (3, 'John', '10,00'), (4, 'Gabe', '245.00'), (5, 'Gabe', NULL), (6, 'Gabe', '0.00') SELECT id, John, Gabe FROM ( SELECT id, rep, TotalAmt = CASE WHEN id IN (1, 4, 6) THEN 'RC(1000)' ELSE SUM(CAST(amt AS DECIMAL(16,2))) END FROM @TestData GROUP BY id, rep ) x PIVOT ( SUM(x.TotalAmt) FOR rep IN ([John],[Gabe])) As SalesRep