Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Group cascading records

Author  Topic 

ryan.gillies
Starting Member

27 Posts

Posted - 2014-03-10 : 10:58:07
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!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-10 : 11:18:27
[code]
; 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,
c.AccountChainID
from Accounts a
inner join rcte c on a.OldAccount = c.CurrentAccount
)
select *
from rcte
order by AccountChainID, CurrentAccount
[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

ryan.gillies
Starting Member

27 Posts

Posted - 2014-03-10 : 14:25:04
Recursive CTE, perfect, thank you.
Go to Top of Page
   

- Advertisement -