Help in SQL Conversion query

Kenny Gua 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
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 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.


2 additional answers

Sort by: Most helpful
  1. Viorel 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?

    1 person found this answer helpful.
    0 comments No comments

  2. Olaf Helper 42,926 Reputation points
    2024-06-24T06:23:30.47+00:00
    1 person found this answer helpful.
    0 comments No comments