Post reply

Combining all rows from a single column without COALESCE

  • September 9, 2004 at 3:06 pm

    #60426

    Hi,

    I wanted to combine all rows from a single column, seperated by a comma. I found an example to do that, it is like this.

    declare @names varchar(500)

    select @names = COALESCE(@names + ',', '') + fname from Users

    select @names

    This is working fine. But I did not understand why do we have to use COALESCE function. I tried with out using it.

    declare @names varchar(500)

    select @names = @names + ',' + fname from Users

    select @names

    But this is not working. It is returning nothing, just NULL. Can anyone explain me why this is happening.

    Thanks.

  • September 9, 2004 at 11:04 pm

    #522438

    Lookup CONCAT_NULL_YIELDS_NULL in BOL.

  • September 10, 2004 at 3:00 am

    #522478

    You can do it without COALESCE if you use an ISNULL function instead:

    declare @names varchar(500)

    select @names = IsNull(@names,'') + ',' + IsNull(fname,'') from Users

    select @names

    But as adrian_rf pointed out. If you concat 'XYV' + NULL then you get NULL.

    ISNULL is a more appropriate function here, as it is returns one or the other value.

    COALESCE is best when you have more than two values to try and you want the first non-null value... eg: SELECT MyPrice = COALESCE(SpecialPrice, DiscountedPrice, IndustryPrice, TradePrice, RetailPrice)

    Julian Kuiters
    juliankuiters.id.au

  • September 10, 2004 at 8:13 am

    #522555

    What the COALESCE or ISNULL does for you is, that it sets the starting value to empty string instead of NULL, that's all. If you declare a variable, it has the value of NULL. If you then add something to NULL, the result is also NULL.

    You can modify your code this way to get rid of NULL value without using COALESCE/ISNULL :

    declare @names varchar(500)

    select @names = ''

    select @names = @names + ',' + fname from Users

    select @names

    This is precisely the same, and it works fine.

    HTH, Vladan

  • September 10, 2004 at 8:25 am

    #522558

    Julian,

    Personally I tend to avoid ISNULL altogether; COALESCE behaves identically with only two values, and is a standard function as opposed to the proprietary ISNULL. One less thing to change should you ever decide to port to a different DBMS.

    --
    Adam Machanic
    whoisactive

  • September 10, 2004 at 8:59 am

    #522566

    If any of the names might be NULL, you need to do it this way:

    declare @names varchar(500)

    select @names = ''

    select @names = @names + ISNULL(',' + fname, '') from Users

    select @names

    The subtle difference between this and the original COALESCE version is that this version will have a leading comma character.  You could eliminate the leading comma and handle NULL values this way:

    declare @names varchar(500)

    select @names = COALESCE(@names + ',' + fname, fname, @names) from Users

    select @names

  • September 10, 2004 at 2:08 pm

    #522629

    Thanks all for your reply. It was helpful.

Viewing 7 posts - 1 through 6 (of 6 total)

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