Dynamically create ranges from numeric sequences

Question

I have a table like the following:

+----+-----+-----+
| ID | GRP | NR  |
+----+-----+-----+
|  1 | 1   | 101 |
|  2 | 1   | 102 |
|  3 | 1   | 103 |
|  4 | 1   | 105 |
|  5 | 1-2 | 106 |
|  6 | 1-2 | 109 |
|  7 | 1-2 | 110 |
|  8 | 2   | 201 |
|  9 | 2   | 202 |
| 10 | 3   | 300 |
| 11 | 3   | 350 |
| 12 | 3   | 351 |
| 13 | 3   | 352 |
+----+-----+-----+

I wanted to create a view which groups this list by GRP and concatenates values in NR. Is it possible to dynamically detect sequences and shorten them into ranges? Like 1, 2, 3, 5 would become 1-3, 5.

So the result should look like this:

+-----+--------------------+
| GRP |        NRS         |
+-----+--------------------+
| 1   | 101 - 103, 105     |
| 1-2 | 106, 109 - 110     |
| 2   | 201 - 202          |
| 3   | 300, 350 - 352     |
+-----+--------------------+

What i got now is simply concatenate values, so the table above would become this:

+-----+--------------------+
| GRP |        NRS         |
+-----+--------------------+
| 1   | 101, 102, 103, 105 |
| 1-2 | 106, 109, 110      |
| 2   | 201, 202           |
| 3   | 300, 350, 351, 352 |
+-----+--------------------+

Here's the actual statement:

DECLARE @T TABLE
(
    ID INT IDENTITY(1, 1)
  , GRP VARCHAR(10)
  , NR INT
)
INSERT INTO @T
VALUES ('1',101),('1',102),('1',103),('1',105)
      ,('1-2',106),('1-2',109), ('1-2',110)
      ,('2',201),('2',202)
      ,('3',300),('3',350),('3',351),('3',352)

SELECT * FROM @T

;WITH GROUPNUMS (RN, GRP, NR, NRS) AS 
(
    SELECT 1, GRP, MIN(NR), CAST(MIN(NR) AS VARCHAR(MAX)) 
    FROM @T
    GROUP BY GRP

    UNION ALL

    SELECT CT.RN + 1, T.GRP, T.NR, CT.NRS + ', ' + CAST(T.NR AS VARCHAR(MAX))
    FROM @T T
    INNER JOIN GROUPNUMS CT ON CT.GRP = T.GRP 
    WHERE T.NR > CT.NR
)
SELECT NRS.GRP, NRS.NRS
FROM GROUPNUMS NRS
INNER JOIN (
    SELECT GRP, MAX(RN) AS MRN 
    FROM GROUPNUMS 
    GROUP BY GRP
) R
ON NRS.RN = R.MRN AND NRS.GRP = R.GRP
ORDER BY NRS.GRP

Can anyone tell me if it's easily possible to do something like that? Would be great if anyone has an idea and would like to share it.

Answer 1

Please check my try:

DECLARE @T TABLE
(
    ID INT IDENTITY(1, 1)
  , GRP VARCHAR(10)
  , NR INT
)
INSERT INTO @T
VALUES ('1',101),('1',102),('1',103),('1',105)
      ,('1-2',106),('1-2',109), ('1-2',110)
      ,('2',201),('2',202)
      ,('3',300),('3',350),('3',351),('3',352)

SELECT * FROM @T

;WITH T1 as
(
    SELECT GRP, NR, ROW_NUMBER() over(order by GRP, NR) ID FROM @T
)
,T as (
    SELECT *, 1 CNT FROM T1 where ID=1
    union all
    SELECT b.*, (case when T.NR+1=b.NR and T.GRP=b.GRP then t.CNT 
                        else T.CNT+1 end)
    from T1 b INNER JOIN T on b.ID=T.ID+1
)
, TN as(
    select *, 
        MIN(NR) over(partition by GRP, CNT) MinVal, 
        MAX(NR) over(partition by GRP, CNT) MaxVal
    From T
)
SELECT GRP, STUFF(
    (SELECT distinct ','+(CASE WHEN MinVal=MaxVal THEN CAST(MinVal as nvarchar(10)) ELSE CAST(MinVal as nvarchar(10))+'-'+cast(MaxVal as nvarchar(10)) END)
       FROM TN b where b.GRP=a.GRP
        FOR XML PATH(''),type).value('.','nvarchar(max)'),1,1,'') AS [ACCOUNT NAMES]
FROM TN a GROUP BY GRP