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 am looking to get

If Designation=Formule in Totalisation LineTotalisationId name for PnlId contents number so I should concat Totalisation for all Totalisation else I will simply add the row

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 = t.c.value('.', 'nvarchar(255)') 
WHERE  Ref.designation LIKE '%[0-9]%' 
        OR Ref.designation = '' 
GROUP  BY split.linetotalisationid ;

The code above returns as result

PNLParentId
A

Is there a way to modify it to get totalisation as it is described ?

Answer 1

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

Assuming Panel is your table name and the Line field only have 1 string character

Declare @sql nvarchar(max);


;
set @sql = 
'with cte as
(
 select 
    case
        when patindex(''%[A-Z]%'',Line) > 0
        then
            substring(line,patindex(''%[A-Z]%'',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
TO use @roopesh Sample 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(''%[A-Z]%'',Line) > 0
        then
            substring(line,patindex(''%[A-Z]%'',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 2

This stored procedure comes as a solution for your problem. It is using cursor. May be there is a way to remove the cursor, but could not get till now. So got this solution.

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

Though the table structure was not very much clear to me. But I used the following script to create the table and insert the 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')