Fastest way to replace string for column values SQL Server

Question

I have the following tables:

Table_1

timestamp                  parameter     value
---------------------------------------------------
2015-09-04 18:48:00.000    par01          1
2015-09-04 18:48:00.000    par02          2
2015-09-04 18:48:00.000    par03          3
2015-09-04 18:48:00.000    par04          4
2015-09-04 18:48:00.000    par05          5
2015-09-04 18:48:00.000    par06          6

Table_2

formulaID      formula                 
---------------------------------------------------
1              (par01*par02)
2              (par03*par04)
3              (par05*par06)

I want to replace the values of the table_1 in the formulas of the table_2. Finally, execute the formula. What's the fastest way?

I know how execute the formulas with the following code:

DECLARE @sql VARCHAR(50)
SET @sql = '(2*2)'

EXEC('select ' + @sql)

You need to be more specific. Otherwise you just want AVG() — Amit
– Amit, Commented Sep 4, 2015 at 18:20
@Amit what happen with the answer I saw before with the AVG ? — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Sep 4, 2015 at 18:58
We can not use AVG() in this case. Because formula is: ( par01 + par01 + par03 )/ 3. @Josep Is it correct formula? — Roman Marusyk
– Roman Marusyk, Commented Sep 4, 2015 at 19:55

Rubens Farias · Accepted Answer · 2015-09-07 14:29:22Z

1

I already did this in the past, and it's a bit tricky:

Make sure the formulas encloses the field names within square brackets, like ([par01]*[par02])
Create a dynamic query to pivot all those table_1 values as columns
Combine the pivoted dynamic query with the formula values and it will be "natural" for the SQL to execute

A glimpse on that result would be like this.

;WITH   Table_1 AS
(
        SELECT  *
        FROM
        (       VALUES
                ('2015-09-04 18:48:00.000', 'par01', 1),
                ('2015-09-04 18:48:00.000', 'par02', 2),
                ('2015-09-04 18:48:00.000', 'par03', 3),
                ('2015-09-04 18:48:00.000', 'par04', 4),
                ('2015-09-04 18:48:00.000', 'par05', 5),
                ('2015-09-04 18:48:00.000', 'par06', 6)
        )       Table_1([timestamp], [parameter], [value])
)
SELECT  *
FROM
(
        SELECT  ([par01]*[par02]) as [formulaId=1], --
                ([par03]*[par04]) as [formulaId=2], -- This area must be dynamic
                ([par05]*[par06]) as [formulaId=3]  --
        FROM    Table_1 
        PIVOT   (       SUM(value) FOR
                        [parameter] in
                        (
                            [par01], [par02], [par03], -- This area must
                            [par04], [par05], [par06]  -- be dynamic too
                        )
                )       pvt
)       p
UNPIVOT
(
        Value FOR FormulaId IN 
        (
            [formulaId=1],  -- 
            [formulaId=2],  -- Another dynamic area
            [formulaId=3]   -- 
        )
)       upvt

Please, notice the dynamic areas.

edited Sep 7, 2015 at 14:29

answered Sep 5, 2015 at 11:43

Rubens Farias

58k8 gold badges136 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JosepB Over a year ago

Is it possible to avoid the pivot? In the first row show the result of [formulaId=1] in the second [formulaId=2] and in the last row the result of [formulaId=3]

Rubens Farias Over a year ago

No you can't avoid it, as you'll the columns names to evaluate those formulas. But you can perform a UNPIVOT after it; I updated the answer to reflect this change

Polux2 · Accepted Answer · 2015-09-04 21:51:49Z

0

To retreive a value, you have to use SELECT So your query can be :

DECLARE @sql VARCHAR(500)
SET @sql = '((select value from t where parameter = ''par01'') +
             (select value from t where parameter = ''par02'') +
             (select value from t where parameter = ''par03'')) / 3'


EXEC('select ' +  @sql)

answered Sep 4, 2015 at 21:51

Polux2

6025 silver badges13 bronze badges

Collectives™ on Stack Overflow

Fastest way to replace string for column values SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related