SQL/T-SQL. Create ranges from numbers

Question

I have a table numbers ex :

  nums
  _____
| 54902 |
| 54903 | 
| 54901 | 
| 54904 | 
| 54905 | 
| 3487  | 
| 3457  | 
| 789   | 
| 790   | 
| 54906 | 
| 791   | 
| 76253 | 

I'd like create ranges from this set like (if is this possible...):

54901-54906
3487
3457 
789-791
76253

Any tips?

Answer 1

For obtaining this kind of ranges. First, you should group sequential numbers - to achieve this, you can use gaps-and-islands approach -. After that, you can get max and min numbers for each group easily.

DECLARE @nums TABLE(num int)
INSERT INTO @nums VALUES
( 54902 ),
( 54903 ), 
( 54901 ), 
( 54904 ), 
( 54905 ), 
( 3487  ), 
( 3457  ), 
( 789   ), 
( 790   ), 
( 54906 ), 
( 791   ), 
( 76253 )


SELECT CAST(MIN(num) AS varchar(10)) 
    + CASE WHEN MAX(num) > MIN(num) THEN '-' + CAST(MAX(num) AS varchar(10)) ELSE '' END  
FROM ( 
    SELECT num, num - ROW_NUMBER() OVER (ORDER BY num) AS GRP 
    FROM @nums 
) AS T
GROUP BY GRP

Result:

---------------------
789-791
3457
3487
54901-54906
76253