sql - 动态计算存储在表列中的表达式

Question

我有下表

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

我希望得到

如果 Designation=Formule 中的 PnlId 内容编号的累加 LineTotalizationId 名称，那么我应该连接所有累加的累加，否则我将简单地添加行

我尝试以下代码

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 ;

上面的代码返回结果

PNLParentId
A

有没有办法修改它以获得所描述的总计？

Answer 1

如果你的样本数据代表了你的数据，你可以尝试下面的sql

假设 Panel 是您的表名称，并且 Line 字段只有 1 个字符串字符

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

*/

已更新
使用@roopesh 示例数据 B1 '公式'

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)