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
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);