Azure SQL Database: how to perform calculations on the data I'm querying

Question

I am exporting data from my DataGrid, to a Azure SQL database table. But I need to manipulate and do some math with it.

What I'm hoping is possible to do by query, is:

• Sort data by Column1 (20 entries, with several hundred lines each)
• Add the contents of all the cells in Column 2, of Column 3 and of Column 4
• Create two new columns, X and Y
• Set the content of Column X to be (Column1 * ColumnY / 100)
• Set the content of Column Y to be a 8

I want my table to filled with just 20 lines, the summarization of all the hundreds of line of each entry in Column1.

Trying to get an idea if this is possible or not to do with a query. I would prefer not to do this "manually" in the DataGrid. Would appreciate a pointer or two in how to approach this.

Answer 1

Your data might have CHAR(13) control character as the carriage return (line breaker) to get the values in different lines in a single column.

There are other different control characters available here

Below is the sample (data with 5 rows and Col1 has 2 lines) to calculate the data.

1. Replaced Col1 values from CHAR(13) to comma (‘,’). This will Combines all lines in the column single string with comma separation.

   update #abc
   set Col1 = replace(Col1,CHAR(13),',')
   

2. Add row_number() to get the ID values for all the rows (optional if you have any unique column for later use).

    select *, ROW_NUMBER() over(order by Col1, col2, col3, col4) as rn  
    into #abc1 
    from #abc
    select * from #abc1
   

3. Now split the values of Col1 into multiple rows and sum them by grouping by other columns.

You can perform similar operations on other columns if needed.

select rn, sum(convert(int,S1.value)) as col1, Col2, Col3, Col4, created_date   
into #abc2 
from #abc1 a
cross apply STRING_SPLIT(a.Col1, ',') as S1
group by rn, col2, col3, col4, created_date
order by rn

4. Now add 2 new columns ColX and ColY. As you are hardcoding the value of ColY to '8', you can substitute the ColY value as 8 when calculating ColX.

   select col1, Col2, Col3, Col4
   ,(Col1*8/100) as ColX, convert(int,'8') as ColY
   from #abc2
   order by col1 --Sort by Col1
   

Select the required columns from the list to sorting by Col1 to get the final results.