SQLTeam.com | Weblogs | Forums

I Need Help To get This Query


#1

Hello Team

I have Table Contain
EquationID ID Column
1 1 25
1 2 +
1 3 85
1 4 -
1 5 75
--================
I need To Get
EquationID Equation Equation Value
1 25+85-75 35


#2

Maybe this:

declare @sql varchar(max);

with cte(EquationID,ID,[Column])
  as (select 1,1,'25'
      union all select 1,2,'+'
      union all select 1,3,'85'
      union all select 1,4,'-'
      union all select 1,5,'75'
      union all select 2,1,'2'
      union all select 2,2,'+'
      union all select 2,3,'2'
     )
select @sql=stuff((select ',('
                         +ltrim(str(a.equationid))
                         +','''+replace(replace(b.e,'<e>',''),'</e>','')+''''
                         +','+replace(replace(b.e,'<e>',''),'</e>','')
                         +')'
                     from (select distinct equationid
                             from cte
                          ) as a
                          cross apply (select [column] as e
                                         from cte as b
                                        where b.equationid=a.equationid
                                        order by b.id
                                          for xml path('')
                                      ) as b(e)
                      for xml path('')
                  )
                  ,1
                  ,1
                  ,''
                 )
;
set @sql='create table #equation(id int,eq varchar(max),eqval int);'
        +'insert into #equation(id,eq,eqval) values '+@sql+';'
        +'select * from #equation order by id'
        ;
print @sql;
exec (@sql);

#3

Thank you Mr.bitsmed