CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
user3431083
user3431083

Reputation: 414

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

Upvotes: 1

Views: 47

Answers (3)

Eid Morsy
Eid Morsy

Reputation: 966

I assumed that you have random data on [from] or [to] column - (may be there more data in [from] column not in [to] column and vise-versa, so

if there is no problem with making an order using [from] and [to] column use the below query

with dataTest as
(
 select [From], [to] , ROW_NUMBER() over(order by [From], [to]) lvl from InputTable e
)
 ,dataD as
 (
   select [From] node,(lvl*2)-1 lvl from dataTest
   union all
   select [To] node,(lvl*2) lvl from dataTest
 )
 ,lastD as
 (
  select  node, lvl, ROW_NUMBER() over(partition by node order by lvl) rn from dataD 
 )
 select node, ROW_NUMBER() over(order by lvl) squence  from lastD where rn=1 order by lvl

if your data is typically as you set in the sample you can use

with dataTest as
(
 select [From], [to] ,1 lvl from InputTable e
 where not exists (select 1 from InputTable e2 where e2.[to] = e.[from]) 
 union all
  select e.[from], e.[to], dataTest.lvl + 1
  from dataTest join
  InputTable e  on dataTest.[to] = e.[from]
)
,dataD as
(
 select [From] node,(lvl*2)-1 lvl from dataTest
 union all
 select [To] node,(lvl*2) lvl from dataTest
)
,lastD as
 (
  select  node, lvl, ROW_NUMBER() over(partition by node order by lvl) rn    
  from dataD 
 )
  select node, ROW_NUMBER() over(order by lvl) squence  from lastD where rn=1 order by lvl

Hope these help you or give you an idea.

Upvotes: 0

dybzon
dybzon

Reputation: 1594

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.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270371

Assuming no cycles, you can use a recursive cte:

with cte as (
      select e.from, e.to, 1 as lev
      from example e
      where not exists (select 1 from example e2 where e2.to = e.from) 
      union all
      select e.from, e.to, cte.lev + 1
      from cte join
           example e
           on cte.to = e.from
    )
select e.from, lev
from cte;

Upvotes: 0

Related Questions