Column values into comma seperated

  • Hi Team,

    using below query to convert the columns in to comma seperated.

    SELECT 1264, isnull(col1, '')+' - '+isnull(col2, '')

    +' - '+isnull(col3, '')+' - '+isnull(col4, '')+' - '

    +isnull(col5, '')+' - '+isnull(col6, '')

    FROM unify WHERE id=100

    if col3 value is null, am getting like below. how to avoid (- - ) in middle.

    ABC - DEF - - GG - DKF - KDIL

    Please suggest.

  • Would this work?

    SELECT 1264, isnull(col1+' - ', '') +isnull(col2+' - ', '')

    +isnull(col3 +' - ', '') + isnull(col4+' - ', '')

    +isnull(col5 +' - ', '') + isnull(col6, '')

    FROM unify WHERE id=100

    -- Gianluca Sartori

  • Thank You,

    Its working.

  • Or this

    SELECT 1264,REPLACE( isnull(col1, '')+' - '+isnull(col2, '')

    +' - '+isnull(col3, '')+' - '+isnull(col4, '')+' - '

    +isnull(col5, '')+' - '+isnull(col6, ''),' - - ',' - ')

    FROM unify WHERE id=100

  • All of the solutions provided leave open the possibility of a trailing delimiter, which may not be desirable. This (UNTESTED) solution will make sure there are no leading or trailing delimiters. I also used the FOR XML PATH method for my concatenation.

    SELECT 1264, STUFF( (

    SELECT ' - ' + col1,

    ' - ' + col2,

    ' - ' + col3,

    ' - ' + col4,

    ' - ' + col5,

    ' - ' + col6

    FOR XML PATH(''), TYPE).value('text()', 'NVARCHAR(4000)'), 1, 3, '')

    FROM unify

    WHERE id=100

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 5 posts - 1 through 4 (of 4 total)

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