Replace consective numbers from a string

Question

So if I have a table with values:

Name   ID        
----   ---
James  100
James  101 
James  102
James  200
James  201 
James  202 
Alex   100
Alex   101 
Alex   102
Alex   105 
Alex   106
Alex   109 
Alex   109
Alex   500
Alex   501 
Alex   102

I want the result to look like this

Name   ID        
----   ---
James  100-102, 201-202
Alex   100-102, 105-106, 109, 500-502

I know, I can transform the data in a row by using FOR XML PATH(''). but I am not sure how to replace consecutive numbers with a - from them. Any help in directing me to right direction will be appreciated. thanks.

Answer 1

I used the following, which gets you part of the way there.

USE tempdb;
GO
CREATE TABLE Test
(
    uname VARCHAR(255)
    , num INT
);

INSERT INTO Test (uname, num) VALUES ('Max', 1);
INSERT INTO Test (uname, num) VALUES ('Max', 2);
INSERT INTO Test (uname, num) VALUES ('Max', 3);
INSERT INTO Test (uname, num) VALUES ('Max', 4);
INSERT INTO Test (uname, num) VALUES ('Max', 5);
INSERT INTO Test (uname, num) VALUES ('Max', 11);
INSERT INTO Test (uname, num) VALUES ('Max', 12);
INSERT INTO Test (uname, num) VALUES ('Max', 13);
INSERT INTO Test (uname, num) VALUES ('Max', 14);
INSERT INTO Test (uname, num) VALUES ('Max', 15);

INSERT INTO Test (uname, num) VALUES ('Jen', 22);
INSERT INTO Test (uname, num) VALUES ('Jen', 23);
INSERT INTO Test (uname, num) VALUES ('Jen', 24);
INSERT INTO Test (uname, num) VALUES ('Jen', 25);

SELECT uname
    , CAST(min(num) as varchar(255)) + ' - ' + CAST(max(num) as varchar(255)) AS NumRange
FROM (
    SELECT uname, num - ROW_NUMBER() OVER (PARTITION BY uname ORDER BY num) groupnum, num
    FROM dbo.Test
    ) t
GROUP BY uname, groupnum;

Results:

Here is the sample code provided so kindly by @AaronBertrand

;WITH cte as (
    SELECT uname
        , min(num) As MinNum
        , CAST(min(num) as varchar(255)) + ' - ' + CAST(max(num) as varchar(255)) AS NumRange
    FROM (
        SELECT uname, num - ROW_NUMBER() OVER (PARTITION BY uname ORDER BY num) groupnum, num
        FROM dbo.Test
        ) t
    GROUP BY uname, groupnum
    )
SELECT DISTINCT uname
    , STUFF((
        (
            SELECT ', ' + NumRange
            FROM cte as cte1
            WHERE cte.uname = cte1.uname
            ORDER BY cte1.MinNum
            FOR XML PATH, TYPE
        ).value('.[1]','VARCHAR(255)')
    ), 1, 2, '') as NumRange
FROM cte;

The results:

STUFF is used here to remove the leading , from the results. It does that by replacing the first two leading characters in the concatenated NumRange with a zero-length string ''.