Select in sql server 2005

Question

I have a table:

ID  |  first  |  end
--------------------
a   |      1  |    3
b   |      3  |    8
c   |      8  |   10

I want to choose the following:

 ID  |  first  |  end
---------------------
a-c  |      1  |   10   

But I can’t do it. You are welcome! Help me. Thank!

Answer 1

This works for me:

SELECT MIN(t.id)+'-'+MAX(t.id) AS ID,
       MIN(t.[first]) AS first,
       MAX(t.[end]) AS [end]
  FROM dbo.YOUR_TABLE t

But please do not use reserved end words for column names .

Answer 2

I believe that you can do this using a recursive generic table expression , especially if you do not expect very long chains of records

WITH Ancestors AS
(
    SELECT
        InitRow.[ID] AS [Ancestor],
        InitRow.[ID],
        InitRow.[first],
        InitRow.[end],
        0 AS [level],
        '00000' + InitRow.[ID] AS [hacky_level_plus_ID]
    FROM
        YOUR_TABLE AS InitRow
    WHERE
        NOT EXISTS
        (
            SELECT * FROM YOUR_TABLE AS PrevRow
            WHERE PrevRow.[end] = InitRow.[first]
        )
    UNION ALL
    SELECT
        ParentRow.Ancestor,
        ChildRow.[ID],
        ChildRow.[first],
        ChildRow.[end],
        ParentRow.level + 1 AS [level],
        -- Avoids having to build the recursive structure more than once.
        -- We know we will not be over 5 digits since CTEs have a recursion
        -- limit of 32767.
        RIGHT('00000' + CAST(ParentRow.level + 1 AS varchar(4)), 5)
            + ChildRow.[ID] AS [hacky_level_plus_ID]
    FROM
        Ancestors AS ParentRow
        INNER JOIN YOUR_TABLE AS ChildRow
            ON ChildRow.[first] = ParentRow.[end]
)
SELECT
    Ancestors.Ancestor + '-' + SUBSTRING(MAX([hacky_level_plus_ID]),6,10) AS [IDs],
-- Without the [hacky_level_plus_ID] column, you need to do it this way:
--  Ancestors.Ancestor + '-' +
--      (SELECT TOP 1 Children.ID FROM Ancestors AS Children
--      WHERE Children.[Ancestor] = Ancestors.[Ancestor]
--      ORDER BY Children.[level] DESC) AS [IDs],
    MIN(Ancestors.[first]) AS [first],
    MAX(Ancestors.[end]) AS [end]
FROM
    Ancestors
GROUP BY
    Ancestors.Ancestor
-- If needed, add OPTION (MAXRECURSION 32767)

A brief explanation of what each part does:

WITH Ancestors AS (...) Common Table ( ) Ancestors. SELECT : , .

SELECT - , . Ancestors , , , YOUR_TABLE. .

- SELECT, , . GROUP BY, Ancestor, - MIN MAX.

. , . , , . ( , , .)

, , , ( level - , 1 , ), , . max level a MAX, level.

CTE , , , MAXRECURSION. - 100. , CTE .

. first , first == end, , , , CTE .

. , , . , .