Find sequence number in SQL Server

Question

This is my table in SQL Server :

number     grade    weight
--------------------------
  1         1         185
  2         1         179
  3         3         191
  4         3         192
  5         3         193
  6         1         194
  7         1         196
  8         1         188
  9         1         187
  10        1         200
  11        2         201
  12        2         202
  13        2         203
  14        2         191
  15        2         187

and I want this output in SQL Server without any temp table :

Number     grade     weight
----------------------------
 1-2         1        364
 3-5         3        576
 6-10        1        965
 11-15       2        984

Does anyone know how I can do this?

Answer 1

This is a gaps-and-island problem. I think the simplest approach is to take the difference between number and a computed row_number():

select 
    concat(min(number), '-', max(number)) number,
    grade,
    sum(weight) weight
from (
    select 
        t.*,
        row_number() over(partition by grade order by number) rn
    from mytable t
) t
group by grade, number - rn
order by min(number)

Demo on DB Fiddle:

number | grade | weight
:----- | ----: | -----:
1-2    |     1 |    364
3-5    |     3 |    576
6-10   |     1 |    965
11-15  |     2 |    984