SQL Recursive CTE: Finding objects linked by property

by

You’re running into an infinite loop caused by cycles within your data, e.g.: 1 > 2 > 3 > 2 > … . The solution is to keep track of the rows that have already been “consumed”. Due to limitations in CTEs, this has to be done by including the history within each CTE row, e.g. by assembling the path followed to arrive at each row. You can uncomment the , Path on the final select to see what is going on.

-- Sample data.
declare @ACC as Table ( AccNo Int, Property Char );
insert into @ACC values
  ( 1, 'A' ), ( 1, 'B' ), ( 2, 'A' ), ( 2, 'C' ), ( 3, 'C' ), ( 4, 'D' );
select * from @ACC;

-- Recursive CTE.
with Groups as (
  select distinct AccNo, AccNo as LinkedAccNo,
    Cast( '|' + Cast( AccNo as VarChar(10) ) + '|' as VarChar(1024) ) as Path
    from @ACC
  union all
  select G.AccNo, A.AccNo, Cast( Path + Cast( A.AccNo as VarChar(10) ) + '|' as VarChar(1024) )
    from Groups as G inner join -- Take the latest round of new rows ...
      @ACC as AP on AP.AccNo = G.LinkedAccNo inner join -- ... and get the   Property   for each ...
      @ACC as A on A.Property = AP.Property -- ... to find new linked rows.
      where G.Path not like '%|' + Cast( A.AccNo as VarChar(10) ) + '|%' )
  select AccNo, LinkedAccNo -- , Path
    from Groups
    order by AccNo, LinkedAccNo;

More Related Contents:

  1. Comma separated results in SQL
  2. How to check if a column exists in a SQL Server table
  3. Understanding PIVOT function in T-SQL
  4. Using T-SQL, return nth delimited element from a string
  5. Get first day of week in SQL Server
  6. Which are more performant, CTE or temporary tables?
  7. Using RegEx in SQL Server
  8. Infinite loop CTE with OPTION (maxrecursion 0)
  9. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  10. Confused about UPDLOCK, HOLDLOCK
  11. Common Table Expression, why semicolon?
  12. INSERT INTO vs SELECT INTO
  13. SQL Server 2008 Empty String vs. Space
  14. How do I use the CONCAT function in SQL Server 2008 R2?
  15. How to set a default value for an existing column
  16. Select / Insert version of an Upsert: is there a design pattern for high concurrency?
  17. TSQL: How to convert local time to UTC? (SQL Server 2008)
  18. SQL Server: combining multiple rows into one row
  19. How do I find a stored procedure containing ?
  20. Recursive sum in tree structure
  21. How to convert number of minutes to hh:mm format in TSQL?
  22. Convert decimal time to hours and minutes
  23. SQL Server 2008 – How do i return a User-Defined Table Type from a Table-Valued Function?
  24. How do I move a table into a schema in T-SQL
  25. Instead of trigger in SQL Server loses SCOPE_IDENTITY?
  26. How Can I Detect and Bound Changes Between Row Values in a SQL Table?
  27. How to wait for 2 seconds?
  28. What are row, page and table locks? And when they are acquired?
  29. Generate MD5 hash string with T-SQL
  30. Query times out when executed from web, but super-fast when executed from SSMS

Leave a Comment