为给定的动态字符串准备SUM函数

作为SQL Server 2008,您首先需要创建一个SplitString函数(它已经包含在SQL Server 2016和forward中),如下所示:

T-SQL split string

然后您可以计算您的子句:

select 'sum(' + Name + ', as ' + Name
from SplitString(@Str)

最后,您只需要连接所有这些行,例如添加for xml path('')

select 'sum(' + Name + ', as ' + Name + ','
from SplitString(@Str)
for xml path('')

试试这个

DECLARE @Str AS TABLE ([Str] varchar(max) )
INSERT INTO @Str
SELECT  '[A-B],[B-C],[C-D],[D-E]' 


;WITH CTE
AS
(
SELECT  'SUM( '+Split.a.value('.','nvarchar(100)')+' ) AS 'AS [Str],Split.a.value('.','nvarchar(100)') AS [Str1]

FROM
(
SELECT  CAST('<S>'+(REPLACE([Str],',','</S><S>')+'</S>') AS XML ) AS [Str]
FROM @Str
)AS A
CROSS APPLY [Str].nodes('S') AS Split(a)
)

SELECT  STUFF((SELECT DISTINCT ', '+ CONCAT([Str], [Str1])
FROM CTE 
FOR XML PATH ('')),1,1,'') AS ExpectedResult

ExpectedResult
-------------------
 SUM( [A-B] ) AS [A-B], SUM( [B-C] ) AS [B-C], SUM( [C-D] ) AS [C-D], SUM( [D-E] ) AS [D-E]

完成任务的简单方法

    Declare @str varchar(max) = '[A-B],[B-C],[C-D],[D-E]'
        , @Main varchar(max)=''


        select  @Main += ',sum('+a+')'
         from (select distinct value as a from STRING_SPLIT(@str , ',')) as Splt 
        set @Main= stuff(@Main ,1,1,'')
        print @Main

Result : sum([A-B]),sum([B-C]),sum([C-D]),sum([D-E])