Dynamically evaluate an expression stored in a table column.

Question

I have the following table

PnlId LineTotalisationId Designation Totalisation 1 A Gross Fees Formule A01+A02+A03+A04+A05 2 A01 GF1 Comptes B01+B02+B03+B04+B05 3 A02 GF2 Comptes C01+C02+C03+C04+C05 4 A03 GF3 Comptes 99991 5 A04 GF4 Comptes 99996 6 A05 GF5 Comptes 999995 14 B1 Perm Comptes 12+14+25 

I'm looking to get

If Obvalue = Formula in the Sweepstakes LineTotalisationId is the name for the PnlId content number, so I have to do the Totalization for all Totalisation else, I just add the line

I try the following code

 SELECT Ref.*, CASE WHEN Charindex('+', Ref.totalisation) > 0 THEN '+' WHEN Charindex('/', Ref.totalisation) > 0 THEN '/' WHEN Charindex('*', Ref.totalisation) > 0 THEN '*' WHEN Charindex('*', Ref.totalisation) > 0 THEN '-' END AS Operator INTO ##ttresults FROM [dbo].[pnlreference] Ref WHERE [typetotalisation] = 'Formule' AND totalisation <> '' AND designation NOT LIKE '%[0-9]%' AND designation != '' SELECT split.linetotalisationid AS PNLParentId, NULL AS Totalisation INTO ##tempresults FROM (SELECT tbl.designation, tbl.linetotalisationid, tbl.typetotalisation, tbl.pnlid, tbl.totalisation, Cast('<t>' + Replace(tbl.totalisation, tbl.operator, '</t><t>') + '</t>' AS XML) x, tbl.operator FROM ##ttresults AS tbl) split CROSS apply x.nodes('/t') t(c) INNER JOIN [dbo].[pnlreference] Ref ON Ref.linetotalisationid = tcvalue('.', 'nvarchar(255)') WHERE Ref.designation LIKE '%[0-9]%' OR Ref.designation = '' GROUP BY split.linetotalisationid ; 

The above code returns the result

 PNLParentId A 

Is there a way to change it to get a totalization as described?

Answer 1

This stored procedure is suitable for solving your problem. He uses the cursor. Maybe there is a way to remove the cursor, but so far it has failed. So this decision turned out.

 CREATE Procedure [dbo].[spGetResult] As Begin declare @curPNL cursor declare @pnlid int declare @Line varchar(10), @TotalisationId varchar(20), @Totalisation varchar(50) declare @spresult table(PNLId int, Line varchar(10), TotalisationId varchar(20), result varchar(4000)); --declare the cursor set @curPNL = cursor for select PnlId, Line, TotalisationId, totalisation from PNLTable where designation = 'Formule' open @curPNL Fetch Next From @curPNL into @pnlId, @Line, @TotalisationId, @Totalisation While @@FETCH_STATUS = 0 Begin declare @nsql nvarchar(4000); declare @table table(tname varchar(50)); declare @result varchar(4000) delete from @table --get the totalisation data for specific column set @nsql = 'select totalisation from PNLTable Where Line in (''' + replace(@Totalisation,'+',''',''') + ''')'; print 'Calling child' insert into @table exec(@nsql); set @result = ''; if not exists (select 1 from @table) Begin set @result = replace(@Totalisation,'+','|') End else Begin --get the values of totalisation in a pipe separated string select @result = case when @result = '' then '' else @result + '|' end + tname from @table; End --insert the values in the temporary table insert into @spresult(PNLId, Line, TotalisationId, result) select @pnlid, @Line, @TotalisationId, @result Fetch Next From @curPNL into @pnlId, @Line, @TotalisationId, @Totalisation End close @curPNL deallocate @curPNL select * from @spresult; End 

Although the structure of the table was not very clear to me. But I created the following script to create a table and insert data.

 CREATE TABLE [dbo].[PNLTable]( [PnlId] [int] NOT NULL, [Line] [varchar](10) NULL, [TotalisationId] [varchar](20) NULL, [Designation] [varchar](20) NULL, [Totalisation] [varchar](50) NULL, PRIMARY KEY CLUSTERED ( [PnlId] ASC ) ) 

- insert data

 INSERT [PNLTable] ([PnlId], [Line], [TotalisationId], [Designation], [Totalisation]) VALUES (1, N'A', N'Gross Fees', N'Formule', N'A01+A02+A03+A04+A05'), (2, N'A01', N'GF1', N'Comptes', N'99999') ,(3, N'A02', N'GF2', N'Comptes', N'99998'), (4, N'A03', N'GF3', N'Comptes', N'99991'), (5, N'A04', N'GF4', N'Comptes', N'99996') , (6, N'A05', N'GF5', N'Comptes', N'999995'), (14, N'B1', N'Perm', N'Formule', N'12+14+25') 

Answer 2

If your sample data represents your data, you can try below sql

The assuming panel is the name of your table, and the Line field has only 1 string character

 Declare @sql nvarchar(max); ; set @sql = 'with cte as ( select case when patindex(''%[AZ]%'',Line) > 0 then substring(line,patindex(''%[AZ]%'',Line),1) end as LineId ,Totalisation from panel where line in (' +''''+ (select replace(a.totalisation,'+',''',''') from Panel a where Designation='formule' ) + '''' +') union all select Line as LineId, Totalisation from panel where line not in (' +''''+ (select replace(a.totalisation,'+',''',''') from Panel a where Designation='formule' ) + '''' +') and Designation <> ''Formule'' ' + ') select distinct p.pnlId, p.Line, p.TotalisationId--, p.Designation ,P.Totalisation ,LineId, LTRIM(substring(stuff ( ( select '' | '' + c2.Totalisation from cte c2 where c.LineId = c2.LineId for xml path('''') ) ,1,0,'''' ),3,len(stuff ( ( select '' | '' + c2.Totalisation from cte c2 where c.LineId = c2.LineId for xml path('''') ) ,1,0,'''' ))) ) as Totalisation from cte c right join panel p on c.LineId = p.Line where c.Totalisation is not null ' ; exec(@sql) /* RESULT pnlId Line TotalisationId LineId Totalisation ----------- ----- -------------- ------ -------------------------------------- 1 A Gross Fees A 99999 | 99998 | 99991 | 99996 | 999995 14 B1 Perm B1 12+14+25 */ 

UPDATED
Use @roopesh data Example data B1 'Formule'

 declare @formula nvarchar(max); ; with cte as ( select distinct 1 as id, p.Totalisation from panel2 p where Designation = 'formule' ) select distinct @formula = '''' + Replace(replace(substring(stuff ( ( select ',' + c2.Totalisation from cte c2 where c.id = c2.id for xml path('') ) ,1,0,'' ),2,len(stuff ( ( select ',' + c2.Totalisation from cte c2 where c.id = c2.id for xml path('') ) ,1,0,'' ))),',',''','''),'+',''',''') + '''' from cte c ; Declare @sql nvarchar(max); ; set @sql = ' ;with cte as ( select case when patindex(''%[AZ]%'',Line) > 0 then substring(line,patindex(''%[AZ]%'',Line),1) end as LineId ,Totalisation from panel2 where line in (' + @formula +') union all select Line as LineId, Totalisation from panel2 where line not in (' + @formula +') and Designation <> ''Formule'' ' + ') select distinct p.pnlId, p.Line, p.TotalisationId--, p.Designation , p.totalisation ,LineId, Case when c.totalisation is null and p.designation=''Formule'' then p.totalisation else LTRIM(substring(stuff ( ( select '' | '' + c2.Totalisation from cte c2 where c.LineId = c2.LineId for xml path('''') ) ,1,0,'''' ),3,len(stuff ( ( select '' | '' + c2.Totalisation from cte c2 where c.LineId = c2.LineId for xml path('''') ) ,1,0,'''' ))) ) end as Totalisation from cte c right join panel2 p on c.LineId = p.Line where p.Designation = ''Formule'' ' ; exec(@sql) 

Answer 3

You need to generate a dynamic SQL query using the form from Designation=Formule instead of using a large static query. Then run the code using EXEC .

The request could be something like this:

 SELECT (SELECT Totalisation WHERE Line = 'A01') + (SELECT Totalisation WHERE Line = 'A02') + (SELECT Totalisation WHERE Line = 'A03') + (SELECT Totalisation WHERE Line = 'A04') + (SELECT Totalisation WHERE Line = 'A05') FROM [dbo].[pnlreference] AS Ref WHERE Designation = 'Formule' AND LEFT(Line, 1) = 'A' 

It is possible to split the form content into separate entries and statements and load it into the temp table containing the form (perhabs A01 and A02, etc.) and an identifier. Then generate the code: The code could be something like this:

 EXEC 'SELECT (SELECT Totalisation WHERE Line = ' + CAST((SELECT Formular FROM #formular WHERE id = 0) AS VARCHAR(MAX)) +') ' + CAST((SELECT Operator FROM #formular WHERE id = 1) AS VARCHAR(MAX)) +' (SELECT Totalisation WHERE Line = ' + CAST((SELECT Formular FROM #formular WHERE id = 1) AS VARCHAR(MAX)) +') ' + CAST((SELECT Operator FROM #formular WHERE id = 2) AS VARCHAR(MAX)) +' (SELECT Totalisation WHERE Line = ' + CAST((SELECT Formular FROM #formular WHERE id = 2) AS VARCHAR(MAX)) +') ' + CAST((SELECT Operator FROM #formular WHERE id = 3) AS VARCHAR(MAX)) +' (SELECT Totalisation WHERE Line = ' + CAST((SELECT Formular FROM #formular WHERE id = 3) AS VARCHAR(MAX)) +') ' + CAST((SELECT Operator FROM #formular WHERE id = 4) AS VARCHAR(MAX)) +' (SELECT Totalisation WHERE Line = ' + CAST((SELECT Formular FROM #formular WHERE id = 4) AS VARCHAR(MAX)) +') FROM [dbo].[pnlreference] AS Ref WHERE Designation = ''Formule'' AND LEFT(Line, 1) = ' + LEFT(CAST((SELECT Formular FROM #formular WHERE id = 0) AS VARCHAR(MAX)), 1);