SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Group cascading records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

United Kingdom
27 Posts

Posted - 03/10/2014 :  10:58:07  Show Profile  Reply with Quote
Hi all, for lack of a better word, I need to cascade a series of records in a table that are linked and group them together. Imagine the following table (dbo.Accounts) and values:

CurrentAccount  OldAccount
001             NULL
002             001
003             002
004             NULL
005             004
006             003
007             005

I'd like to be able to create a recordset that creates a chain between the OldAccounts, the CurrentAccount and its future accounts - from the above example I would want to be able to group them as follows:

CurrentAccount  OldAccount  AccountChainID
001             NULL        1
002             001         1
003             002         1
006             003         1

004             NULL        2
005             004         2
007             005         2

I'm struggling to get my head around the logic needed to accomplish this, but its proving tricky!

Does anyone have any thoughts? Thanks!

In (Som, Ni, Yak)

17684 Posts

Posted - 03/10/2014 :  11:18:27  Show Profile  Reply with Quote

; with 
rcte as
	select	CurrentAccount, OldAccount,
		AccountChainID = row_number() over (order by CurrentAccount)
	from	Accounts
	where	OldAccount	is null

	union all

	select	a.CurrentAccount, a.OldAccount,
	from	Accounts a
		inner join rcte c	on	a.OldAccount	= c.CurrentAccount
select	*
from	rcte
order by AccountChainID, CurrentAccount

Time is always against us

Go to Top of Page

Starting Member

United Kingdom
27 Posts

Posted - 03/10/2014 :  14:25:04  Show Profile  Reply with Quote
Recursive CTE, perfect, thank you.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000