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?

`COALESCE` is an `ISNULL` for multiple fallthroughs. You're looking for `CONCAT` — Siyual, Mar 09 '15 at 17:54
`COALESCE(@region + ', ', SPACE(0))` will return either @region concatenated with a comma or just a comma by itself. It will never return `SPACE(0)` since there is always something non-null in the first parameter. Perhaps you meant `CASE WHEN @region IS NOT NULL THEN @region + ',' END + @regionNext` — JNevill, Mar 09 '15 at 17:55
@JNevill That's not entirely accurate. If `@region` is `NULL`: `NULL` + anything is still `NULL` — Siyual, Mar 09 '15 at 17:56

Pரதீப் · Accepted Answer · 2015-03-09T18:19:07.060

9

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

edited Mar 09 '15 at 18:19

answered Mar 09 '15 at 17:56

Pரதீப்

91,748
19
131
172

Upvoted but this may produce strange results when column `Name` contains XML reserved chars. – Bogdan Sahlean Mar 09 '15 at 18:36
1

@BogdanSahlean - Yeah its true. Also good to know about the alternative. – Pரதீப் Mar 09 '15 at 18:50

score 1 · Answer 2 · answered Mar 09 '15 at 17:55

1

You could use a CTE, which might be a bit cleaner:

;WITH CTE AS
( SELECT Region,
         Name,
         rn = ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name)
  FROM Table
)
SELECT c1.Region + ',' + c2.Region,
       c1.Name
FROM CTE c1
INNER JOIN CTE c2 ON c2.rn = c1.rn + 1

answered Mar 09 '15 at 17:55

Phoenix

1,881
5
20
28

it doesn't output the results as requested by an SO – Malcolm May 31 '16 at 08:48

SQL Server Coalesce data set

2 Answers2