AAAE_N AAAE_N - 3 months ago 14
SQL Question

Generating epression string in SQL to add values of a column in pivot

I want to get this string for using in my other queries

@cols = 'ISNULL(myColumnName_value_1,0) + ISNULL(myColumnName_value_2,0) + ... + ISNULL(myColumnName_value_N,0)'


and
myColumnName_value_1
OR
myColumnName_value_2
, ... which could be anything.

And my query is:

DECLARE @cols AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ' + ISNULL(' + (c.myColumnName) +',0)'
FROM myTableName c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

print @cols


but I can't get what I want.

my desired output of print:

ISNULL(myColumnName_value_1,0) + ISNULL(myColumnName_value_2,0) + ... + ISNULL(myColumnName_value_N,0)

And "..." because i dont know the name or number of columns.

Answer

Here is your modified query.

DECLARE @cols AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ' + ISNULL(' + QUOTENAME(c.myColumnName) +',0)'
        FROM myTableName c
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,2,1,'')

print @cols