Concat the first and last sequential line number with a hyphen

Our table stores the line numbers of an order. Is it possible to concat them into a string, where the first and last sequential lines are separated by a hyphen, and non-sequential lines are separated by a comma? Some of our orders can have 100+ lines of the same item, and instead of listing every line separately, we would like to condense them to "Lines: 1-100", or "Lines: 1-20, 25, 30-50, 75-100", etc...

Source

Current Query

Using STRING_AGG, I've gotten them grouped by individual lines, but I can't figure out how to identify the sequential portions and pull their first/last values for the hyphen. Could LAG and LEAD be used in conjunction with STRING_AGG?

SELECT
    CONCAT('Lines: ', STRING_AGG(Line, ', ') WITHIN GROUP (ORDER BY Line)) AS [Line],
    Item
FROM Table
GROUP BY Item

Current Result

Desired Result

This sounds like gaps and islands. Here's a potential solution:

SELECT  Item
,   STRING_AGG(CAST(label AS NVARCHAR(MAX)), ', ') WITHIN GROUP(ORDER BY sorter) AS Label
FROM    (
    SELECT  CASE WHEN count(*) = 1 THEN CAST(MIN(Line) AS VARCHAR(10)) ELSE CONCAT(MIN(Line), '-', MAX(Line)) END AS label, Item
    ,   MIN(Line) AS sorter
    FROM    (
        SELECT  *
        , ROW_NUMBER() OVER(ORDER BY line) AS r1
        , ROW_NUMBER() OVER(PARTITION BY item ORDER BY line) AS r2
        FROM
        (
            VALUES  (1, N'Apple')
            ,   (2, N'Apple')
            ,   (3, N'Apple')
            ,   (4, N'Orange')
            ,   (5, N'Apple')
            ,   (6, N'Orange')
            ,   (7, N'Apple')
            ,   (8, N'Apple')
            ,   (9, N'Orange')
            ,   (10, N'Orange')
        ) t (Line,Item)
        ) x
    GROUP BY r1 - r2, item
    ) x
GROUP BY item

The row_number diff calculates the "groupings" of each item, and then the rest just builds a proper label, either a single Line or a line range if count(*) > 1.

Finally the STRING_AGG puts it all together.

In your real solution, you also probably need to group/partition by OrderNumber.

Sample output: