This is my SQL table structure for example:
Is there a way to calculate the final result in Result column. The result should be
210,
1190,
-630
respectively.
I prefer non-dynamic query, so that I can use it in view/function. Also the table may have 1000's of rows with different configurations.
This is my solution:
It uses dynamic query and loop. Not a very efficient way.
Thank you
Edit:
Table structure:
select *,'result' as Result from dbo.temp_20220721
sid val1 sign1 val2 sign2 val3 sign3 val4 Result
1 10.00000000 * 20.00000000 - 30.00000000 40.00000000 result
2 10.00000000 - 20.00000000 30.00000000 * 40.00000000 result
3 10.00000000 - 20.00000000 * 30.00000000 - 40.00000000 result
My solution:
create table dbo.#temp1(
Result numeric(18,8),
)
declare @sql varchar(max), @id int
select top 1 @sql = CONCAT('select ',val1,sign1,val2,sign2,val3,sign3,val4),@id=sid from dbo.temp_20220721
while @@rowcount > 0
begin
INSERT INTO dbo.#temp1 exec(@sql)
select top 1 @sql = CONCAT('select ',val1,sign1,val2,sign2,val3,sign3,val4) ,@id=sid from dbo.temp_20220721 where sid > @id order by sid;
end;
select * from dbo.#temp1
CodePudding user response：
I did not try all scenarios but this query can be work.
DECLARE @TempTable TABLE ([sid] int,
val1 decimal(18,2),
sign1 VARCHAR(2),
val2 decimal(18,2),
sign2 VARCHAR(2),
val3 decimal(18,2),
sign3 VARCHAR(2),
val4 decimal(18,2))
INSERT INTO @TempTable VALUES
(1, 10.00, '*', 20.00, '-' , 30.00, ' ', 40.00) ,
(2, 10.00, '-', 20.00, ' ' , 30.00, '*', 40.00) ,
(3, 10.00, '-', 20.00, '*' , 30.00, '-', 40.00) ,
(4, 10.00, '*', 20.00, ' ' , 30.00, '/', 40.00)
;WITH C1 AS (
select [sid],
CASE WHEN sign1 IN( '*', '/') THEN 0 ELSE val1 END AS val1,
CASE WHEN sign1 IN( '*', '/') THEN ' ' ELSE sign1 END AS sign1,
CASE WHEN sign1 = '*' THEN val1 * val2
WHEN sign1 = '/' THEN val1 / val2
ELSE val2 END AS val2,
sign2,val3, sign3, val4
from @TempTable
)
, C2 AS (
select [sid], val1,sign1,
CASE WHEN sign2 IN( '*', '/') THEN 0 ELSE val2 END AS val2,
CASE WHEN sign2 IN( '*', '/') THEN sign1 ELSE sign2 END AS sign2,
CASE WHEN sign2 = '*' THEN val2 * val3
WHEN sign2 = '/' THEN val2 / val3
ELSE val3 END AS val3,
sign3, val4
from C1
)
, C3 AS (
select [sid], val1,sign1,val2, sign2,
CASE WHEN sign3 IN( '*', '/') THEN 0 ELSE val3 END AS val3,
CASE WHEN sign3 IN( '*', '/') THEN sign2 ELSE sign3 END AS sign3,
CASE WHEN sign3 = '*' THEN val3 * val4
WHEN sign3 = '/' THEN val3 / val4
ELSE val4 END AS val4
from C2
),
C4 AS (
select *,
CASE
WHEN sign1 = ' ' THEN val1 val2
WHEN sign1 = '-' THEN val1 - val2
END AS r1
from C3
)
, C5 AS (
select *,
CASE
WHEN sign2 = ' ' THEN r1 val3
WHEN sign2 = '-' THEN r1 - val3
END AS r2
from C4
)
, C6 AS (
select *,
CASE
WHEN sign3 = ' ' THEN r2 val4
WHEN sign3 = '-' THEN r2 - val4
END AS r3
from C5
)
SELECT T.*, C6.r3 result FROM C6
INNER JOIN @TempTable T ON C6.sid = T.sid
Result:
sid val1 sign1 val2 sign2 val3 sign3 val4 result
------ -------- ----- ------- ----- ------- ----- ------- ----------
1 10.00 * 20.00 - 30.00 40.00 210.0000
2 10.00 - 20.00 30.00 * 40.00 1190.0000
3 10.00 - 20.00 * 30.00 - 40.00 -630.0000
4 10.00 * 20.00 30.00 / 40.00 200.7500
CodePudding user response：
You can use 'Computed Column', please take a look at: