Gaps and islands. I've done something similar in sql once, hope I can find that SO post tomorrow.
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);
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 )
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
Try it online!
Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment's permalink.
Hide child comments as well
Confirm
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
Gaps and islands. I've done something similar in sql once, hope I can find that SO post tomorrow.
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:
Then, a cte to group the consecutive values together:
And finally query that cte:
Try it online!