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

SQLFiddle demo

with TRes 
as 
(
select T.GRP,T.NR NR,
CASE WHEN T1.NR IS NULL and T2.NR is null
      THEN CAST(T.NR as VARCHAR(MAX))
     WHEN T1.NR IS NULL and T2.NR IS NOT NULL
      THEN '-'+CAST(T.NR as VARCHAR(MAX))
     WHEN T1.NR IS NOT NULL and T2.NR IS NULL
      THEN CAST(T.NR as VARCHAR(MAX))+'-'
END AS NR_GRP


from T
left join T T1 on T.Grp=T1.Grp and t.Nr+1=t1.Nr
left join T T2 on T.Grp=T2.Grp and t.Nr-1=t2.Nr

WHERE T1.NR IS NULL or T2.NR IS NULL

)
SELECT
   GRP,
   REPLACE(
   substring((SELECT ( ',' + NR_GRP)
                           FROM TRes t2
                           WHERE t1.GRP = t2.GRP
                           ORDER BY 
                              GRP,
                              NR
                           FOR XML PATH( '' )
                          ), 2, 10000 )
   ,'-,-','-')  
FROM TRes t1
GROUP BY GRP