Help in SQL Conversion query

Question

• How I can convert Total column from varchar to numeric to show result in numeric with two decimal using SELECT query.

Create table #temp (Num1 numeric (6,2), Num2 numeric (6,2), Num3 numeric (6,2), Num4 numeric (6,2), Total varchar(200))

insert into #temp values (2.2, 3.25, 4.25, 6.25, 'Num1+Num2+Num3+Num4')

insert into #temp values (2, 3, 4, 5, 'Num1+Num2+Num3+Num4')

insert into #temp values (1, 2, 3, 3, 'Num1+Num2+Num3+Num4')

I Want to get the following Result from SQL query.

Select Num1, Num2, Num3, Num4,Num1+Num2+Num3+Num4 as Total

Num1 Num2 Num3 Num4 Total

2.2 3.25 4.25 6.25 15.95

2 3 4 5 14

1 2 3 3 9

Answer 1

Here is a solution:

DECLARE @sql nvarchar(MAX)
SELECT @sql = string_agg(convert(nvarchar(MAX), 
            concat('
            SELECT Num1, Num2, Num3, Num4, Total= ', Total, '
                    FROM (VALUES(', Num1, ',', Num2, ',',  Num3, ',', Num4, 
                      ')) AS V(Num1, Num2, Num3, Num4)')), ' UNION ALL ')
FROM  #temp
PRINT @sql
EXEC sp_executesql @sql

If it looks complicated, it is because you are trying to something SQL is not intended for.

Answer 2

In your SELECT statement, the Total column is already decimal: numeric(9,2). To hide the “.00” parts, try this:

select Num1, Num2, Num3, Num4, format(Num1 + Num2 + Num3 + Num4, '0.##') as Total
from #temp

Maybe you actually want to evaluate the arithmetic expressions?

Answer 3

Similar question at

https://techcommunity.microsoft.com/t5/sql-server/help-in-varchar-column-calculation/m-p/4173033