Select subsequent numbers in the table with result in ranges

Question

I need to deliver a result in the following format: [12-14][20][34-35]

Is there any more intelligent or elegant approach than iterating cursor on the select of this column?

The select might be quite large.

Table:

|---------------------|
|      col1           |
|---------------------|
|      12             |
|---------------------|
|      13             |
|---------------------|
|      14             |
|---------------------|
|      20             |
|---------------------|
|      34             |
|---------------------|
|      35             |
|---------------------|

Sounds like a gaps and island problem, with `LEAD`. What *have* you tried so far, what other questions with the same problem (there are many) did you look at and what about them didn't you understand? — Thom A, Feb 02 '20 at 13:43
@Larnu I can do it with the cursor iteration, I'm asking here for the better idea. — dllhell, Feb 02 '20 at 13:46
It is a gaps and islands problem, possibly followed by an aggregation to generate the ranges. — Tim Biegeleisen, Feb 02 '20 at 13:49
Does this answer your question? https://stackoverflow.com/q/18699137/2029983 — Thom A, Feb 02 '20 at 13:55

score 1 · Accepted Answer · answered Feb 02 '20 at 14:21

As others have said, this is a gaps and islands problem so you need to work out which numbers are the start of a sequence and which numbers are the end of a sequence.

I did this using two subqueries and joining them together:

SELECT  CASE
            WHEN startSeq.Col1 = endSeq.Col1
            THEN '[' + CONVERT(VARCHAR(2),startSeq.Col1) +']'
            ELSE '[' + CONVERT(VARCHAR(2),startSeq.Col1) + '-' + CONVERT(VARCHAR(2),endSeq.Col1) + ']'
        END
FROM    (
            SELECT  Col1,
                    ROW_NUMBER() OVER(ORDER BY Col1) AS RowN
            FROM    Nums a
            WHERE   NOT EXISTS (
                                    SELECT  Col1
                                    FROM    Nums b
                                    WHERE   b.Col1 = a.Col1 - 1
                                )
        ) startSeq
        JOIN (
                SELECT  Col1,
                        ROW_NUMBER() OVER(ORDER BY Col1) AS RowN
                FROM    Nums a
                WHERE   NOT EXISTS (
                                        SELECT  Col1
                                        FROM    Nums b
                                        WHERE   b.Col1 = a.Col1 + 1
                                    )
                ) endSeq
            ON startSeq.RowN = endSeq.RowN

Thank you good sir, it helped me to understand more quickly this trouble. — dllhell, Feb 02 '20 at 14:56

Select subsequent numbers in the table with result in ranges

1 Answers1