Help in SQL Conversion query

431 Reputation points
2024-06-23T23:34:19.0633333+00:00
• 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

SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
65 questions

1. 106K Reputation points MVP
2024-06-24T21:51:19.8466667+00:00

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.

1. 114.4K Reputation points
2024-06-24T03:31:23.7933333+00:00

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?