AAAE_N AAAE_N - 4 months ago 9
SQL Question

Generating expression 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 don't 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