SQL Server Coalesce data set

Question

I have a query that returns a dataset that looks like this:

Region                           Name
-------------------------------------------------------------
Canada                           Jim 
Canada                           Michael
Canada                           Andy
Mexico                           Jim
Mexico                           Michael
Mexico                           Kevin

but I want to return it like this instead:

Region                           Name
-------------------------------------------------------------
Canada, Mexico                   Jim
Canada, Mexico                   Michael
Canada                           Andy
Mexico                           Kevin

My cursor query attempts to coalesce Region names when Names are equal

OPEN RegionCursor

FETCH NEXT
FROM RegionCursor
INTO @regionNext
    ,@NameNext

SET @name = @NameNext;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @NameNext != @name
    BEGIN
        INSERT @RegionTable
        SELECT @region
            ,@name

        SELECT @region = @regionNext
            ,@name = @NameNext
    END
    ELSE
        SET @region = COALESCE(@region + ', ', SPACE(0)) + @regionNext;

    FETCH NEXT
    FROM RegionCursor
    INTO @regionNext
        ,@NameNext
END

INSERT @RegionTable
SELECT @region
    ,@name

CLOSE RegionCursor;

DEALLOCATE RegionCursor;

When this is run, however, it returns the original dataset with nothing coalesced. How should I modify my query to return the desired dataset?

Answer 1

You don't need cursor use XML PATH trick to do this.

SELECT Name,
       LEFT(cs.Region, Len(cs.Region) - 1) AS Region                           
FROM   Yourtable a
       CROSS APPLY (SELECT Region + ','
                    FROM   Yourtable B
                    WHERE  a.name = b.name
                    FOR XML PATH('')) cs (Region)
GROUP  BY Name,
          LEFT(cs.Region, Len(cs.Region) - 1) 

• FIDDLE DEMO