Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Unanswered: How to concatenate several fields together with commas?

    I have a hierarchy of product categories and I want to string them together to show the complete path (breadcrumbs) to the item. Each category/subcat must be separated buy a comma. The catch is that not all items have the same number of cats/subcats. Here is what I am currently doing, but as you can see, this results in extra commas where the subcats are null.

    Code:
     create table #ItemCat(
            itemNo int,
            Cat varchar(50),
            SubCat1 varchar(50),
            SubCat2 varchar(50)
            )
          INSERT INTO #ItemCat VALUES 
            (1,'Kitchen','Appliances',null),
            (2,'Kitchen','Cookware','Pots'),
            (3,'Outdoor',null,null),
            (4,'Bedroom','Furniture','Dresser')
            
          SELECT itemNo
            ,isnull(Cat,'') + ' ,' + isnull(SubCat1,'') + ' ,' + isnull(SubCat2,'') AS Hierarchy
          FROM #ItemCat
    Is there some way to concatenate these, separated by commas, but ignoring the NULL fields? For example, ItemNo 1 should show "Kitchen, Appliances"

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    In this case, you just need to move the comma into the ISNULL function.
    Code:
    SELECT itemNo
            ,isnull(Cat,'') + isnull(' ,' + SubCat1,'') +  isnull(' ,' +SubCat2,'') AS Hierarchy
          FROM #ItemCat
    Remember: NULL + anything = NULL, so if SubCat1 is null, the concatenation of ', ' + SubCat1 is null.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT itemNo
    ,  isnull(Cat,'') + Coalesce(', ' + SubCat1,'') + Coalesce(', ' + SubCat2,'') AS Hierarchy
       FROM #ItemCat
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Great thanks. I actually just figured it out using a method very similar to Pat's:

    Code:
       SELECT
            coalesce(Cat, '')
            + Coalesce(', ' + SubCat1, '')
            + Coalesce(', ' + SubCat2, '')
       FROM #itemCat

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •