Inserting 6 nullable comma separated fields into a single value?

  • Goalie35

    SSC Eights!

    Points: 854

    I have 6 nullable fields, which I need to insert into a single, comma separated value.  The query I'm working with however needs to emphasize performance, so I was just curious what's the best way to achieve the following, taking performance into account?

    CREATE TABLE #MyTable (Id INT, Val1 VARCHAR(10), Val2 VARCHAR(10), Val3 VARCHAR(10), Val4 VARCHAR(10), Val5 VARCHAR(10), Val6 VARCHAR(10))

    INSERT INTO #MyTable SELECT 1, 'abc', 'def', 'ghi', NULL, 'mno', NULL
    INSERT INTO #MyTable SELECT 2, 'this', NULL, 'is', 'just', 'a', 'test'

    --the below select statement doesn't work correctly
    SELECT Id, (ISNULL(Val1, '') + ', ' + ISNULL(Val2, '') + ', ' + ISNULL(Val3, '') + ', ' + ISNULL(Val4, '') + ', ' + ISNULL(Val5, '') + ', ' + ISNULL(Val6, '')) as FinalValue
    FROM #MyTable

    DROP TABLE #MyTable

    The problem with the code above, is commas are automatically included, but I need to exclude the comma if that particular Val is null.

    So currently, my result is as follows:

    ID           Final Value

    --------------------------------------

    1             abc, def, ghi, , mno,

    2            this, , is, just, a, test

    But I actually need the following:

    ID           Final Value

    --------------------------------------

    1             abc, def, ghi, mno

    2            this, is, just, a, test

    Thanks

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716629

    Use COALESCE()

    SELECT
    Id
    , FinalValue = (ISNULL(Val1, '') + ', ' + ISNULL(Val2, '') + ', '
    + ISNULL(Val3, '') + ', ' + ISNULL(Val4, '') + ', '
    + ISNULL(Val5, '') + ', ' + ISNULL(Val6, '')
    )
    , FinalValue = (COALESCE(Val1 + ', ', '') + COALESCE(Val2 + ', ', '') + COALESCE(Val3 + ', ', '')
    + COALESCE(Val4 + ', ', '') + COALESCE(Val5 + ', ', '') + COALESCE(Val6 + ', ', '')
    )
    FROM #MyTable;
  • @Taps

    SSC Eights!

    Points: 841

    Came up with this but I am curious to know if there is a more elegant way to achieve it

    SELECT  Id ,
    CASE WHEN RIGHT(( ISNULL(Val1 + ',', '') + ISNULL(Val2 + ',', '')
    + +ISNULL(Val3 + ',', '') + ISNULL(Val4 + ',', '')
    + ISNULL(Val5 + ',', '') + ISNULL(Val6, '') ), 1) = ','
    THEN LEFT (( ISNULL(Val1 + ',', '') + ISNULL(Val2 + ',', '')
    + +ISNULL(Val3 + ',', '') + ISNULL(Val4 + ',',
    '')
    + ISNULL(Val5 + ',', '') + ISNULL(Val6, '') ),
    LEN(ISNULL(Val1 + ',', '') + ISNULL(Val2 + ',', '')
    + +ISNULL(Val3 + ',', '') + ISNULL(Val4 + ',',
    '')
    + ISNULL(Val5 + ',', '') + ISNULL(Val6, ''))
    - 1)
    ELSE ( ISNULL(Val1 + ',', '') + ISNULL(Val2 + ',', '')
    + +ISNULL(Val3 + ',', '') + ISNULL(Val4 + ',', '')
    + ISNULL(Val5 + ',', '') + ISNULL(Val6, '') )
    END FinalValue
    FROM #MyTable;
  • Andrey

    Say Hey Kid

    Points: 691

    SELECT Id, CONCAT_ws (',',VAL1,VAL2,VAL3,VAL4,VAL5,VAL6) 
    FROM #MyTable

     

    or

    SELECT Id, (ISNULL(Val1+ ', ' , '') + ISNULL(Val2+ ', ', '')  + ISNULL(Val3+ ', ' , '')+ ISNULL(Val4+ ', ' , '')  + ISNULL(Val5+ ', ' , '')  + ISNULL(Val6, '')) as FinalValue 
    FROM #MyTable

     

  • drew.allen

    SSC Guru

    Points: 76658

    Most of these methods don't correctly handle situations where the final value is null (leaving a trailing comma).  It's simpler to handle this using leading commas rather than trailing commas.  Here is a solution.

    SELECT mt.Id, STUFF(COALESCE(', ' + Val1, '') + COALESCE(', ' + Val2, '') + COALESCE(', ' + Val3, '') + COALESCE(', ' + Val4, '') + COALESCE(', ' + Val5, '') + COALESCE(', ' + Val6, ''), 1, 2, '') AS csv
    FROM #MyTable AS mt

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88173

    Use CONCAT and plus:

    Declare @val1 varchar(10) = 'abc'
    , @val2 varchar(10) = 'def'
    , @val3 varchar(10) = Null
    , @val4 varchar(10) = 'jkl';

    Select concat(@val1 + ',', @val2 + ',', @val3 + ',', @val4);

    If you want to insure that blanks are treated as nulls you can wrap each value with NULLIF.  You also have to check for the last value being NULL or blank and if so you need to remove the last comma:

    Something like this:

    Declare @val1 varchar(10) = 'abc'
    , @val2 varchar(10) = 'def'
    , @val3 varchar(10) = Null
    , @val4 varchar(10) = '';

    Select FinalValue = iif(right(v.val, 1) = ',', left(v.val, len(v.val) - 1), v.val)
    From (
    Values (concat(nullif(@val1, '') + ',', nullif(@val2, '') + ',', nullif(@val3, '') + ',', nullif(@val4, '')))
    ) v(val);

    Or this:

    Declare @val1 varchar(10) = 'abc'
    , @val2 varchar(10) = 'def'
    , @val3 varchar(10) = Null
    , @val4 varchar(10) = '';

    Select t.FinalValue
    From (Values (concat(nullif(@val1, '') + ',', nullif(@val2, '') + ',', nullif(@val3, '') + ',', nullif(@val4, '')))) v(val)
    Cross Apply (Values (iif(right(v.val, 1) = ',', left(v.val, len(v.val) - 1), v.val))) t(FinalValue);

     

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Jeff Moden

    SSC Guru

    Points: 995144

    drew.allen wrote:

    Most of these methods don't correctly handle situations where the final value is null (leaving a trailing comma).  It's simpler to handle this using leading commas rather than trailing commas.  Here is a solution.

    SELECT mt.Id, STUFF(COALESCE(', ' + Val1, '') + COALESCE(', ' + Val2, '') + COALESCE(', ' + Val3, '') + COALESCE(', ' + Val4, '') + COALESCE(', ' + Val5, '') + COALESCE(', ' + Val6, ''), 1, 2, '') AS csv
    FROM #MyTable AS mt

    Drew

    As with all else, "It Depends".  In most cases, I want that trailing comma if the last value is null.  In fact, I'd want empty strings for embedded values that have a null.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • drew.allen

    SSC Guru

    Points: 76658

    Jeff Moden wrote:

    drew.allen wrote:

    Most of these methods don't correctly handle situations where the final value is null (leaving a trailing comma).  It's simpler to handle this using leading commas rather than trailing commas.  Here is a solution.

    SELECT mt.Id, STUFF(COALESCE(', ' + Val1, '') + COALESCE(', ' + Val2, '') + COALESCE(', ' + Val3, '') + COALESCE(', ' + Val4, '') + COALESCE(', ' + Val5, '') + COALESCE(', ' + Val6, ''), 1, 2, '') AS csv
    FROM #MyTable AS mt

    Drew

    As with all else, "It Depends".  In most cases, I want that trailing comma if the last value is null.  In fact, I'd want empty strings for embedded values that have a null.  

    The OP's desired results specifically exclude trailing commas.

    Drew

    • This reply was modified 5 months ago by  drew.allen.

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88173

    drew.allen wrote:

    Most of these methods don't correctly handle situations where the final value is null (leaving a trailing comma).  It's simpler to handle this using leading commas rather than trailing commas.  Here is a solution.

    SELECT mt.Id, STUFF(COALESCE(', ' + Val1, '') + COALESCE(', ' + Val2, '') + COALESCE(', ' + Val3, '') + COALESCE(', ' + Val4, '') + COALESCE(', ' + Val5, '') + COALESCE(', ' + Val6, ''), 1, 2, '') AS csv
    FROM #MyTable AS mt

    Drew

    FYI - if any of the values are empty string you will have extra commas and could have an extra column at the end.  To handle that - and remove just the leading comma:

    Declare @val1 varchar(10) = 'abc'
    , @val2 varchar(10) = 'def'
    , @val3 varchar(10) = Null
    , @val4 varchar(10) = '';

    Select t.FinalValue
    From (Values (concat(',' + nullif(@val1, ''), ',' + nullif(@val2, ''), ',' + nullif(@val3, ''), ',' + nullif(@val4, '')))) v(val)
    Cross Apply (Values (stuff(v.val, 1, 1, ''))) t(FinalValue);

     

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Jeff Moden

    SSC Guru

    Points: 995144

    drew.allen wrote:

    Jeff Moden wrote:

    drew.allen wrote:

    Most of these methods don't correctly handle situations where the final value is null (leaving a trailing comma).  It's simpler to handle this using leading commas rather than trailing commas.  Here is a solution.

    SELECT mt.Id, STUFF(COALESCE(', ' + Val1, '') + COALESCE(', ' + Val2, '') + COALESCE(', ' + Val3, '') + COALESCE(', ' + Val4, '') + COALESCE(', ' + Val5, '') + COALESCE(', ' + Val6, ''), 1, 2, '') AS csv
    FROM #MyTable AS mt

    Drew

    As with all else, "It Depends".  In most cases, I want that trailing comma if the last value is null.  In fact, I'd want empty strings for embedded values that have a null.  

    The OP's desired results specifically exclude trailing commas. Drew

    Crud. My apologies, Drew.  I totally blew it.  I didn't even look at the original post.  You're absolutely correct.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Goalie35

    SSC Eights!

    Points: 854

    Thanks to everyone for the help!  I ultimately went with Drew's solution, as I need to ensure there was no trailing comma.

    SELECT mt.Id, STUFF(COALESCE(', ' + Val1, '') + COALESCE(', ' + Val2, '') + COALESCE(', ' + Val3, '') + COALESCE(', ' + Val4, '') + COALESCE(', ' + Val5, '') + COALESCE(', ' + Val6, ''), 1, 2, '') AS csv
    FROM #MyTable AS mt

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

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