How to create a comma delimited string

  • Hello,

    Here is a sample data I am using.

    CREATE TABLE #temp

    (ae1 varchar,

    ae2 varchar,

    ae3 varchar,

    ae4 varchar)

    INSERT INTO #temp SELECT 1,2,null,null

    INSERT INTO #temp SELECT null,NULL,3,4

    INSERT INTO #temp SELECT null,null,NULL,4

    INSERT INTO #temp SELECT NULL,NULL,3,null

    INSERT INTO #temp SELECT 1,null,null,null

    I need to return this as

    ae1ae2ae3ae4Error Codes

    12NULLNULL1, 2

    NULLNULL343, 4

    NULLNULLNULL44

    NULLNULL3NULL3

    1NULLNULLNULL1

    I can't seem to figure out how to concatenate all the fields properly, especially when there are null values

    I tried Stuff() but wasn't having much luck although I figure my syntax was wrong.

    Any hints are appreciated!

  • There may be a more elegant solution that looks and/or performs better but this query gives the results you asked for:

    CREATE TABLE #temp

    (

    ae1 VARCHAR,

    ae2 VARCHAR,

    ae3 VARCHAR,

    ae4 VARCHAR

    )

    INSERT INTO #temp SELECT 1,2,null,null

    INSERT INTO #temp SELECT null,NULL,3,4

    INSERT INTO #temp SELECT null,null,NULL,4

    INSERT INTO #temp SELECT NULL,NULL,3,null

    INSERT INTO #temp SELECT 1,null,null,null

    WITH cte(ae1, ae2, ae3, ae4, concat_string)

    AS (

    SELECT ae1,

    ae2,

    ae3,

    ae4,

    ISNULL(ae1 + ',', '') + ISNULL(ae2 + ',', '') + ISNULL(ae3 + ',', '') + ISNULL(ae4 + ',', '') AS concat_string

    FROM #temp

    )

    SELECT ae1,

    ae2,

    ae3,

    ae4,

    LEFT(concat_string, LEN(concat_string) - 1) AS concat_string

    FROM cte ;

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • whoops... opc.three has it covered.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • One tweak I would make to mine is to trade in the two separate function calls to LEFT and LEN for one call to STUFF:

    CREATE TABLE #temp

    (

    ae1 VARCHAR,

    ae2 VARCHAR,

    ae3 VARCHAR,

    ae4 VARCHAR

    ) ;

    INSERT INTO #temp SELECT 1,2,NULL,NULL ;

    INSERT INTO #temp SELECT NULL,NULL,3,4 ;

    INSERT INTO #temp SELECT NULL,NULL,NULL,4 ;

    INSERT INTO #temp SELECT NULL,NULL,3,NULL ;

    INSERT INTO #temp SELECT 1,NULL,NULL,NULL ;

    INSERT INTO #temp SELECT NULL,NULL,NULL,NULL ;

    WITH cte ( ae1, ae2, ae3, ae4, concat_string )

    AS (

    SELECT ae1,

    ae2,

    ae3,

    ae4,

    ISNULL(',' + ae1, '') + ISNULL(',' + ae2, '') + ISNULL(',' + ae3, '') + ISNULL(',' + ae4, '') AS concat_string

    FROM #temp

    )

    SELECT ae1,

    ae2,

    ae3,

    ae4,

    STUFF(concat_string, 1, 1, '') AS concat_string

    FROM cte ;

    Edit

    PS and at that point who needs the cte?

    SELECT ae1,

    ae2,

    ae3,

    ae4,

    STUFF(ISNULL(',' + ae1, '') + ISNULL(',' + ae2, '') + ISNULL(',' + ae3, '') + ISNULL(',' + ae4, ''), 1, 1, '') AS concat_string

    FROM #temp ;

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • Thanks so much, I knew I was close but the last post really cleared up how the STUFF function works!

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

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