I'm trying to get the results of the top query (if null insert 0, comma between fields). Can get it done if I hard code field names but need it done dynamically. Thank you for any assistance!
IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test
CREATE TABLE #Test
(MyId int, p201812 int, p201811 int, p201810 int)
INSERT INTO #Test
SELECT 123, 10, 20, 30 UNION ALL
SELECT 456, 10, 0, 30 UNION ALL
SELECT 789, 11, 21, 31 UNION ALL
SELECT 987, 21, 31, CAST(NULL as int)
--this is my goal (but dynamically built)
--all fields concat except MyId, if null insert 0, comma between fields
SELECT MyId, CONCAT(ISNULL(p201812,0), ', ', ISNULL(p201811,0), ', ', ISNULL(p201810,0)) as MyConcat FROM #Test
--dynamically build
DECLARE @cols as nvarchar(MAX)
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(column_name)
FROM
(SELECT table_name, column_name, ordinal_position, data_type
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE table_name LIKE '#test%'
AND ordinal_position NOT IN (1)
) cols
ORDER BY ordinal_position
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)'),1,1,'')
--SELECT @cols as ColString
DECLARE @sqlStr nvarchar(MAX)
SET @sqlStr = N' SELECT MyId, CONCAT(' + @cols + ') FROM #test'
--PRINT (@sqlStr)
EXEC (@sqlStr)