Get specific result without using loop

I've a table in which data is like this for single user

ID - Number - SubNumber - Name

1 101 201101 Jack

2 101 201102 Jack

3 101 201103 Jack

4 101 201107 Jack

5 101 201111 Jack

6 101 201112 Jack

7 101 201113 Jack

8 101 201161 Jack

9 101 201162 Jack

10 101 201163 Jack

11 101 201164 Jack

12 101 201165 Jack

I want to get records like this without using any kind of loop.

Number - Name - SubNumber

101 Jack (201101-201103, 201107, 201111-201113, 201161-201165)

Currently I'm able to get records in form of this

Number - Name - SubNumber

101 Jack (201101,201102,201103, 201107, 201111.201112,201113, 201161,201162,201163,201164,201165)

Query to get upper result is

SELECT  Number, Name
,STUFF((SELECT ', ' + CAST(SubNumber AS VARCHAR(50)) [text()]
     FROM [Table] 
     WHERE Number= t.Number
     FOR XML PATH(''), TYPE)
    .value('.','NVARCHAR(MAX)'),1,2,' ') SubNumber
FROM [Table] t
GROUP BY Number,Name
having Number= '101'

Am totally stuck over here. Any kind of help will be appreciated.

First add a row_number() value to the mix, because I'm guessing you can't rely on ID to have no gaps.

WITH NumberedRows AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Number, Name ORDER BY SubNumber) AS RowNum
  FROM [Table]
)

--now compare RowNum to SubNumber. When the difference changes, there's been a gap in the sequence of SubNumber values.

, Diffs AS (
  SELECT *, SubNumber - RowNum AS TheDiff
  FROM NumberedRows
)
, Ranges AS (
  SELECT Number, Name,
    MIN(SubNumber) AS StartRange, 
    MAX(SubNumber) AS EndRange
  FROM Diffs
  GROUP BY Number, Name, TheDiff
)
, RangeStrings AS (
  SELECT Number, Name, 
    CASE WHEN StartRange = EndRange 
         THEN CAST(StartRange AS VARCHAR(10))
         ELSE CAST(StartRange AS VARCHAR(10)) + '-' + CAST(EndRange AS VARCHAR(10))
    END AS RangeString
  FROM Ranges
)

--And then just do your concatenation as you have already solved. :)

SELECT ...
FROM RangeStrings;

StackExchange Database Administrators Q#97282, answer score: 3