I have a table which consists of column that stores dynamic formula, and the value for each formula is stored in another table column. I want to substitute the formula with the value stored on the other table column and calculate the results of the replaced formula.
Like for example:
Thank you in advance for your help 
-- create table defs and populate data
CREATE TABLE Table_Formula(id int not null PRIMARY KEY, country varchar(20) not null, Formula varchar(100) not null);
INSERT Table_Formula VALUES
(1,'Germany','[AMOUNT]*(1+[FEE])*(1+[MAIN])')
,(2,'India','[AMOUNT]*(1+[MAIN])')
,(3,'Korea','[AMOUNT]*(1+[FEE])*(1+[SUB])');
CREATE TABLE Table_Rate(id int not null PRIMARY KEY, country varchar(20) not null
, Category_1 varchar(10) not null, Value_1 money not null
, Category_2 varchar(10) null, Value_2 money null
, Category_3 varchar(10) null, Value_3 money null);
INSERT Table_Rate VALUES
(1,'Germany','AMOUNT',600,'FEE',50,'MAIN',70)
,(2,'India','AMOUNT',700,'MAIN',60,null,null)
,(3,'Korea','AMOUNT',500,'FEE',40,'SUB',99);
DECLARE @sql nvarchar(max)=N'';
-- unpivot category and value columns into 2 normalized columns
-- re-pivot categories and their values into column names
-- apply formula using generated column names
-- construct SQL with these statements to calculate formula
SELECT @sql+=FORMATMESSAGE(N'%s SELECT id, country, %s computed_value FROM cte2 WHERE id=%d ',
CASE WHEN id=1
THEN N'WITH cte AS(SELECT a.id,a.country, cats.* FROM Table_Rate a
CROSS APPLY(VALUES(Category_1,Value_1), (Category_2,Value_2), (Category_3,Value_3)) AS cats(cat,val)
WHERE cats.cat IS NOT NULL)
,cte2 as (SELECT id, country, [AMOUNT],[FEE],[MAIN],[SUB]
FROM (SELECT id, country, cat, val FROM cte) a
PIVOT(MAX(val) FOR cat IN ([AMOUNT],[FEE],[MAIN],[SUB])) b)
'
ELSE N'UNION ALL' END,Formula,id)
FROM Table_Formula
ORDER BY id
-- execute generated SQL
EXEC(@sql);
This doesn't include the Formula column or the replacement values in the expression, it just calculates the formula.
Thanks @robert_volk, its works perfectly fine. just a qq, is there any way where I don't need to indicate all Category (e.g. AMOUNT, FEE, MAIN, SUB, etc.) and will just automatically include all category?
There is:
WITH cte AS(SELECT a.id,a.country, cats.* FROM Table_Rate a
CROSS APPLY(VALUES(Category_1,Value_1), (Category_2,Value_2), (Category_3,Value_3)) AS cats(cat,val)
WHERE cats.cat IS NOT NULL)
SELECT DISTINCT cat INTO #Cats from CTE
DECLARE @cats nvarchar(max)=N'';
SELECT @cats=STUFF((SELECT N',' + QUOTENAME(cat) FROM #cats FOR XML PATH('')),1,1,N'')
DECLARE @sql nvarchar(max)=N'';
-- unpivot category and value columns into 2 normalized columns
-- re-pivot categories and their values into column names
-- apply formula using generated column names
-- construct SQL with these statements to calculate formula
SELECT @sql+=FORMATMESSAGE(N'%s SELECT id, country, %s computed_value FROM cte2 WHERE id=%d ',
CASE WHEN id=1
THEN N'WITH cte AS(SELECT a.id,a.country, cats.* FROM Table_Rate a
CROSS APPLY(VALUES(Category_1,Value_1), (Category_2,Value_2), (Category_3,Value_3)) AS cats(cat,val)
WHERE cats.cat IS NOT NULL)
,cte2 as (SELECT id, country, ' + @cats + N'
FROM (SELECT id, country, cat, val FROM cte) a
PIVOT(MAX(val) FOR cat IN (' + @cats + N')) b)
'
ELSE N'UNION ALL' END,Formula,id)
FROM Table_Formula
ORDER BY id
-- execute generated SQL
EXEC(@sql);@robert_volk, I'am having a problem with the UNION, since I have 30 Category and Values. and when I print @sql, value was truncated causing an error when I try to exec the @sql, is there any way to solved this error? Thanks
There's a limit to how much output can be printed or SELECTed. In SSMS, go to Tools, Options, Query Results, and under Results to Text you'll find an option for increasing column output (up to 8192). I'm not sure if this affects PRINT however, if in doubt use SELECT.
As long as you use nvarchar(max) and concatenate with the technique published here, it should still EXEC safely as long as you don't copy/paste it.
