CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Gowtham Ramamoorthy
Gowtham Ramamoorthy

Reputation: 896

How to replace strings using group by clause in sql?

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 ex: 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 query but couldn't achieve it. How can this be done ?

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

Upvotes: 0

Views: 930

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Unfortunately, this type of replacement requires recursion:

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

Upvotes: 1

G.Nagababu
G.Nagababu

Reputation: 43

SELECT t1.yearmonth,
   formula=REPLACE( (SELECT value AS [data()]
       FROM table t2
      WHERE t2.YEARMONTH= t1.YEARMONTH
      ORDER BY value
        FOR XML PATH('')
        ), ' ', '+')
  FROM table t1
  GROUP BY YEARMONTH ;

if u need column idnum try this

declare @t table (idnum int identity(1,1),formula varchar(100),Yearmonth date)
insert into @t
    SELECT t1.yearmonth,
   formula=REPLACE( (SELECT value AS [data()]
       FROM table t2
      WHERE t2.YEARMONTH= t1.YEARMONTH
      ORDER BY value
        FOR XML PATH('')
        ), ' ', '+')
  FROM table t1
  GROUP BY YEARMONTH ;

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

Use FOR XML PATH for concatenation:

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

Upvotes: 1

Related Questions