Click here to Skip to main content
14,880,288 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have the below table where I need to replace the string in the column'Formula' with the matching input 'VALUE' column based on the group 'Yearmonth'.
IDNUM  formula                      INPUTNAME      VALUE    YEARMONTH
1      imports(398)+imports(399)    imports(398)    17.000  2003:1
2      imports(398)+imports(399)    imports(398)    56.000  2003:2
3      imports(398)+imports(399)    imports(399)    15.000  2003:1
4      imports(398)+imports(399)    imports(399)    126.000 2003:2


For eg :From the above table i need the output as
Idnum  Formula        Yearmonth
1.    17.00 +15.00    2003:1
2.    56.00 +126.00   2003:2

I tried with the below different query but coludnt achieve it. How can this be done ?

Type1 :
    REPLACE(FORMULA, INPUTName, AttributeValue) AS realvalues, 
FROM table1 
GROUP BY yearmonth

USING XML PATH... In this case it got worked but I need to replace only the strings with the values and not to stuff the strings based on mathematcal operation.(Because the formula might be of any type).

    FORMULA = 
        (SELECT STUFF(
            (SELECT ' +' + CONVERT(VARCHAR(10), Value)
            FROM Table1
            FOR XML PATH(''))
        ,1, 2, '')),
FROM Table1 t1

I'm getting only the null value as output.

;with t as (
      select t.*,
             row_number() over (partition by yearmonth order by idnum) as seqnum,
             count(*) over (partition by yearmonth) as cnt
      from table t
     cte as (
      select t.seqnum, t.yearmonth, t.cnt,
             replace(formula, inputname, value) as formula
      from t
      where seqnum = 1
      union all
      select cte.seqnum, cte.yearmonth, cte.cnt,
             replace(formula, t.inputername, t.value)
      from cte join
           on cte.yearmonth = t.yearmonth and cte.seqnum = t.seqnum + 1
select row_number() over (order by (select null)) as id,
from cte
where seqnum = cnt
Kornfeld Eliyahu Peter 4-Jan-16 2:13am
SQL is for storing/manipulating tabular data - what you trying here is to transfer some code to the SQL - it does not belongs there...
[no name] 4-Jan-16 2:14am
Yes this seems useless forum... I don't belong here...
Kornfeld Eliyahu Peter 4-Jan-16 4:15am
My father - who was a repair-man - used to say, that hammer is one of the most useful tool, but still can't solve any problem with it alone...
IMHO, you try to stretch SQL in a way it wasn't intended to work...
However, it seems to me a bit rushy to say you do not belong here...Remember that I'm only one of millions here, that may answer your problem...So be patient and may get some answer more suitable for you...
AndrewCharlz 5-Jan-16 23:41pm
well said peter
Herman<T>.Instance 4-Jan-16 5:53am
Do you want the formula be 17.00 +15.00 of the sum of that i.e. 32.00?
And an order by on YearMonth could be advisable too
Tomas Takac 5-Jan-16 9:19am
How do you get the list of tokens for each formula. I guess INPUTNAME, VALUE, YEARMONTH are from one table while the formula comes from somewhere else. Now the interesting part is how you match the formula with the INPUTNAME. Do you parse the formula?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900