Dynamically Build Query to Concat

• texpic

  SSCertifiable

  Points: 5885

  More actions

  February 23, 2019 at 3:52 pm

  #379454

  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)


• texpic

  SSCertifiable

  Points: 5885

  More actions

  February 23, 2019 at 3:53 pm

  #2022745

  So concat field will look lie this:
  10, 20, 30
  10, 0, 30
  11, 21, 31
  21, 31, 0

  not like this:
  102030
  10030
  112131
  2131