Dynamically Build Query to Concat

  • texpic

    SSCertifiable

    Points: 5880

    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: 5880

    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

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply