2

I have the following data in rows:

1
2
3
6
7
15
16

I want to write a SELECT statement to return contiguous ranges. Given the sample data above, the output would be:

1,2,3
6,7
15,16
10

Given the following sample data:

DECLARE @Data AS table
(
    data integer PRIMARY KEY
);

INSERT @Data
    (data)
VALUES
    (1),
    (2),
    (3),
    (6),
    (7),
    (15),
    (16);

One way to achieve the result you are after is:

WITH Grouped AS
(
    -- Identify groups
    SELECT 
        D.data,
        grp = D.data - ROW_NUMBER() 
            OVER (ORDER BY D.data)
    FROM @Data AS D
)
SELECT
    STUFF
    (
        (
            -- Concatenate items in the current group
            SELECT 
                [text()] = ',' + CONVERT(varchar(11), G2.data)
            FROM Grouped AS G2
            WHERE 
                G2.grp = Grouped.grp
            ORDER BY 
                G2.data
            FOR XML PATH ('')
        )
        -- Remove initial comma
        , 1, 1, ''
    )
FROM Grouped
GROUP BY 
    Grouped.grp;

Test at SQLfiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.