Vietnamcode Logo

How to join multi row in sql to string

  • Tuesday, October 16, 2012
  • Unknown
  • Example:
    Consider a database table holding names, with three rows:
    Peter
    Paul
    Mary

    Use COALESCE:
    DECLARE @Names VARCHAR(8000) 
    SELECT @Names = COALESCE(@Names + ', ', '') + Name 
    FROM People
    Just some explanation (since this answer seems to get relatively regular views):
    • Coalesce is really just a helpful cheat that accomplishes two things:
    1) No need to initialize @Names with an empty string value.
    2) No need to strip off an extra separator at the end.
    • The solution above will give incorrect results if a row has a NULL Name value (if there is a NULL, the NULL will make @Names NULL after that row, and the next row will start over as an empty string again. Easily fixed with one of two solutions:
    DECLARE @Names VARCHAR(8000) 
    SELECT @Names = COALESCE(@Names + ', ', '') + Name
    FROM People
    WHERE Name IS NOT NULL
    
    or:
    DECLARE @Names VARCHAR(8000) 
    SELECT @Names = COALESCE(@Names + ', ', '') + 
        ISNULL(Name, 'N/A')
    FROM People
    
    Depending on what behavior you want (the first option just filters *NULL*s out, the second option keeps them in the list with a marker message [replace 'N/A' with whatever is appropriate for you]).

    0 comments:

    Post a Comment