Group cascading records - SQL Server Forums

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]

ryan.gillies
Starting Member

27 Posts

Posted - 2014-03-10 : 14:25:04

Recursive CTE, perfect, thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources