I am trying to write a SQL query in SQL Server 2012 to convert a table that has 2 columns
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.