How to concatenate CommaSeperator in sqlserver

Question

I have a Parameter as

@Cols='[Development Opportunities],[Feedback and Coaching],[Goals and Targets Set]'

I want the result set as below:

sum(isnull([Development Opportunities],0)) as [Development Opportunities],
sum(isnull([Feedback and Coaching],0)) as [Feedback and Coaching],
sum(isnull([Goals and Targets Set],0)) as [Goals and Targets Set]

Any ideas? Parameter values are generated dynamically.

score 0 · Answer 1 · edited May 23 '17 at 10:33

You can do it using dynamic sql. So you should make a SQL statement and run it using sp_executesql

First of all we should separate this list to rows and then concatenate them with format we need to generate a SQL statement. Both tasks we can do using XML SQL server features.

To separate this string to elements we use nodes() Method of XML data type.

To use it we should convert this string to XML format. Just replace , with </X><X>:

DECLARE @X XML
SET @X = CAST('<X>'+REPLACE(@Cols,',','</X><X>')+'</X>' as XML)

Then using nodes() split this string to elements:

  SELECT a.c.value('data(.)', 'VARCHAR(100)') as fieldName
    FROM   @X.nodes('X') as a(c)

Then we should concatenate result set to one line formatting it as we need. We use XML PATH()

SELECT @SQL = STUFF
((SELECT ',sum(isnull('+fieldName+',0)) as '+ fieldName
    FROM  separatedTable
    FOR XML PATH(''), TYPE).value('.','NVARCHAR(max)')
,1,1,'')

Now format the full SQL statement and run it:

SET @SQL='SELECT '+@SQL+ ' FROM YOUR_TABLE'
EXECUTE sp_executesql @SQL

Notice that for sp_executesql a command line parameter has to be NVARCHAR() type not just VARCHAR().

SQLFiddle demo

And here is a full code:

DECLARE @Cols varchar(100)
SET @Cols = '[Development Opportunities],[Feedback and Coaching],[Goals and Targets Set]'

DECLARE @SQL nvarchar(max)
DECLARE @X XML

SET @X = CAST('<X>'+REPLACE(@Cols,',','</X><X>')+'</X>' as XML)

;WITH separatedTable AS 
(
  SELECT a.c.value('data(.)', 'VARCHAR(100)') as fieldName
    FROM   @X.nodes('X') as a(c)
) 
SELECT @SQL = STUFF
((SELECT ',sum(isnull('+fieldName+',0)) as '+ fieldName
    FROM  separatedTable
    FOR XML PATH(''), TYPE).value('.','NVARCHAR(max)')
,1,1,'')

SET @SQL='SELECT '+@SQL+ ' FROM YOUR_TABLE'
EXECUTE sp_executesql @SQL

And last but not least: "Run time-compiled Transact-SQL statements can expose applications to malicious attacks, such as SQL injection". So you should check your input parameter list to prevent injection.

How to concatenate CommaSeperator in sqlserver

1 Answers1