DEV Community

Discussion on: Daily Challenge #93 - Range Extraction

Collapse
 
peledzohar profile image
Zohar Peled

Well, I did find that SO post - had to change the solution a bit for the "group must contain more than two members" rule, but that was easy enough - So here's a pure T-SQL solution.

First, table setup:

DECLARE @T AS TABLE
(
    N int
);

INSERT INTO @T VALUES
(-6), 
(-3), (-2), (-1), (0), (1), 
(3), (4), (5), 
(7), (8), (9), (10), (11), 
(14), 
(15), 
(17), (18), (19), (20);
Enter fullscreen mode Exit fullscreen mode

Then, a cte to group the consecutive values together:

With Grouped AS
(
    SELECT N,
           N - DENSE_RANK() OVER(ORDER BY N) As Grp
    FROM @T
)
Enter fullscreen mode Exit fullscreen mode

And finally query that cte:

SELECT STUFF(
(
    SELECT ',' + 
            CASE 
            WHEN COUNT(N) > 2 THEN
               CAST(MIN(N) as varchar(11)) +'-' + CAST(MAX(N) as varchar(11)) 
            WHEN COUNT (N) = 2 THEN
                CAST(MIN(N) as varchar(11)) +',' + CAST(MAX(N) as varchar(11)) 
            ELSE
                CAST(MIN(N) as varchar(11))
            END
    FROM Grouped   
    GROUP BY grp
    FOR XML PATH('')
), 1, 1, '')  As GapsAndIslands
Enter fullscreen mode Exit fullscreen mode

Try it online!