0

Given string:

Note: The following comma separted string is dynamic which comes with any combination.

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

Expected Result:

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

My try:

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

Output:

SUM([A-B]),SUM([B-C]),SUM([C-D]),SUM([D-E])
MAK
  • 6,824
  • 25
  • 74
  • 131
  • This just seems like a bad idea in the first place; it's an injection issue just waiting to happen. I think we would be better off if you started from the beginning. – Thom A Jun 13 '19 at 08:32

3 Answers3

2

Try this

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

Result

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]
Sreenu131
  • 2,476
  • 1
  • 7
  • 18
1

Being SQL Server 2008 you would first need to create an SplitString function (it's already included in SQL Server 2016 and forward), like this one :

T-SQL split string

Then you can calculate your clauses :

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

And finally you only need to concatenate all those rows, for example adding for xml path('')

select 'sum(' + Name + ', as ' + Name + ','
from SplitString(@Str)
for xml path('')
Marc Guillot
  • 6,090
  • 1
  • 15
  • 42
0

Simple way to achieve your task

    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])
Raj
  • 16
  • 1
  • 4