代码之家 › 专栏 › 技术社区 › MAK

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

sql-server-2008-r2 sql-server

MAK · 技术社区 · 7 年前

给定字符串:

注意 :以下逗号分隔的字符串是动态的,可以与任何组合一起使用。

DECLARE @Str varchar(max) = '[A-B],[B-C],[C-D],[D-E]'

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

我的尝试:

SELECT 'SUM('+REPLACE(@Str,',','),SUM(')+')'

输出:

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

2 回复 | 直到 7 年前

Marc Guillot 7 年前

作为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('')

Sreenu131 7 年前

试试这个

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]

Raj 7 年前

完成任务的简单方法

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

推荐文章