user3431083 user3431083 -3 years ago 91
SQL Question

SQL Server 2012 - Convert a From-To columned table into a numbered sequence

I am trying to write a SQL query in SQL Server 2012 to convert a table that has 2 columns

FROM
and
TO
into a table that has a numerical sequence of the described route taken if you logically follow the from-to direction. I have been struggling for hours on this and any hints would be greatly appreciated. Thanks

EXAMPLE:

enter image description here

Answer Source

To answer this I'll have to assume either a sort order or a "base record". I.e. I need to know which record should be the starting point. In this example I have simply hardcoded the value of the base record.

WITH RecursiveCTE AS(
    SELECT 
        [FROM], [TO],
        1 AS SequenceNo
    FROM InputTable
    WHERE [FROM] = 'B' --Hardcoded value to select a base record
    UNION ALL
    SELECT 
        t.[FROM], t.[TO],
        SequenceNo + 1 AS SequenceNo
    FROM RecursiveCTE e
        INNER JOIN InputTable t ON e.[TO] = t.[FROM]
)
--Get all records except the last one via the Recursive CTE
SELECT 
    [FROM] AS [Node],
    SequenceNo
FROM RecursiveCTE
UNION ALL
--Get the last record in a separate query
SELECT TOP 1
    [TO] AS [Node],
    SequenceNo + 1 AS SequenceNo
FROM RecursiveCTE
WHERE SequenceNo = (SELECT MAX(SequenceNo) FROM RecursiveCTE)

The query uses a recursive CTE to get all records except the last one. The last record is added by the UNION ALL statement at the end.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download