Select a range of values

Question

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

Answer 1

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