select in sql server 2005

Question

I have a table follow:

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

I want to select follow:

ID  first  end
a-c  1      10   

But i can't do it. Please! help me. Thanks!

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 words like "end" for column names.

Answer 2

I believe you can do this using a recursive Common Table Expression as follows, especially if you're not expecting 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 quick explanation of what each part does:

The WITH Ancestors AS (...) clause creates a Common Table Expression (basically a subquery) with the name Ancestors. The first SELECT in that expression establishes a baseline: all the rows that have no matching entry prior to it.

Then, the second SELECT is where the recursion kicks in. Since it references Ancestors as part of the query, it uses the rows it has already added to the table and then performs a join with new ones from YOUR_TABLE. This will recursively find more and more rows to add to the end of each chain.

The last clause is the SELECT that uses this recursive table we've built up. It does a simple GROUP BY since we've saved off the original ID in the Ancestor column, so the start and end are a simple MIN and MAX.

The tricky part is figuring out the ID of the last row in the chain. There are two ways to do it, both illustrated in the query. You can either join back with the recursive table, in which case it will build the recursive table all over again, or you can attempt to keep track of the last item as you go. (If building the recursive list of chained records is expensive, you definitely want to minimize the number of times you need to do that.)

The way it keeps track as it goes is to keep track of its position in the chain (the level column -- notice how we add 1 each time we recurse), zero-pad it, and then stick the ID at the end. Then, getting the item with the max level is simply a MAX followed by stripping the level data out.

If the CTE has to recurse too much, it will generate an error, but I believe you can tweak that using the MAXRECURSION option. The default is 100. If you have to set it higher than that, you may want to consider not using a recursive CTE to do this.

This also doesn't handle malformed data very well. If you have two records with the same first or a record where first == end, then this won't work right and you may have to tweak the join conditions inside the CTE or go with another approach.

This isn't the only way to do it. I believe it would be easier to follow if you built a custom procedure and did all the steps manually. But this has the advantage of operating in a single statement.