CODEWITHSUNDEEP
sql-server
Trada
Trada

Reputation: 45

Formula for computed column based on others column

I have a table like this

Formular  | A  | B  | C | ....
----------------------------------------
(A + B)   | 5  | 10 | 8 | ....
(B + C)   | 8  | 5  | 1 | ....
(A - Y)   | 10 | 0  | 9 | ....
(A + Z)   | 2  | 0  | 0 | ....

How can i select to get new table like below

Formular  | A  | B  | C | ....| ToTal
----------------------------------------
(A + B)   | 5  | 10 | 8 | ....| 15
(B + C)   | 8  | 5  | 1 | ....| 6
(A - C)   | 10 | 0  | 9 | ....| 1
(A + Z)   | 10 | 0  | 9 | ....| ...

The ... mean it can have many column and very dynamic

Thanks you very much!

Upvotes: 1

Views: 84

Answers (1)

M.Ali
M.Ali

Reputation: 69504

If you already have a primary key column in your table, skip the steps where I have added a row_number to rows and use your primary key column instead the rest should be the same.

Test Data

CREATE TABLE Formulas (Formular VARCHAR(10), A INT  , B INT , C  INT)
GO
INSERT INTO Formulas VALUES 
('(A + B)'   , 5  , 10 , 8 ),
('(B + C)'   , 8  , 5  , 1 ),
('(A - C)'   , 10 , 0  , 9 ),
('(A + C)'   , 10 , 0  , 9 )
GO

Create Temp Holding table

Select *
      ,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rn
      ,0 AS Total
      INTO ##Temp
FROM Formulas 

ALTER TABLE ##Temp 
ADD [SQL] NVARCHAR(MAX)
GO

Prepare SQL Statements

UPDATE ##Temp
SET [SQL] = 'Select @Total = ' + Formular + ' FROM ##Temp WHERE rn = @RowNumber'  
GO

Execute SQL Statements and Populate Holding Table

Declare @Sql NVARCHAR(MAX) , @RowNumber INT, @Total INT

Declare Cur CURSOR FOR
SELECT rn , [SQL]
FROM ##Temp

OPEN Cur 

 FETCH NEXT FROM Cur INTO @RowNumber , @Sql

WHILE (@@FETCH_STATUS = 0)
BEGIN
    Exec sp_executesql @Sql
                      ,N'@RowNumber INT, @Total INT OUTPUT'
                      ,@RowNumber
                      ,@Total OUTPUT 

     Select   @Total , @Total
      UPDATE ##Temp SET Total = @Total WHERE rn = @RowNumber

    FETCH NEXT FROM Cur INTO @RowNumber , @Sql
END

CLOSE Cur
DEALLOCATE Cur

Final Results

Select Formular, A , B, C, Total 
from ##Temp

╔══════════╦════╦════╦═══╦═══════╗
║ Formular ║ A  ║ B  ║ C ║ Total ║
╠══════════╬════╬════╬═══╬═══════╣
║ (A + B)  ║  5 ║ 10 ║ 8 ║    15 ║
║ (B + C)  ║  8 ║  5 ║ 1 ║     6 ║
║ (A - C)  ║ 10 ║  0 ║ 9 ║     1 ║
║ (A + C)  ║ 10 ║  0 ║ 9 ║    19 ║
╚══════════╩════╩════╩═══╩═══════╝

Upvotes: 1

Related Questions