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'.
SQL
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


</pre>

For eg :From the above table i need the output as
SQL
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 :
SQL
SELECT
    REPLACE(FORMULA, INPUTName, AttributeValue) AS realvalues, 
    yearmonth 
FROM table1 
GROUP BY yearmonth


TYPE 2:
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).

SQL
SELECT
    IDNUM = MIN(IDNUM),
    FORMULA = 
        (SELECT STUFF(
            (SELECT ' +' + CONVERT(VARCHAR(10), Value)
            FROM Table1
            WHERE YEARMONTH = t1.YEARMONTH
            FOR XML PATH(''))
        ,1, 2, '')),
    YEARMONTH
FROM Table1 t1
GROUP BY YEARMONTH


Type3:
I'm getting only the null value as output.

SQL
;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
           t
           on cte.yearmonth = t.yearmonth and cte.seqnum = t.seqnum + 1
    )
select row_number() over (order by (select null)) as id,
       formula
from cte
where seqnum = cnt
Posted
Comments
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