I have the following table:
Account_Number Parent_Account Child_Account
R003247 R000355 R000002
R000355 NULL R003247
R000002 R003247 NULL
R004853 NULL R028636
R004853 NULL R028638
R004853 NULL R028637
R028636 R004853 NULL
R028638 R004853 NULL
R028637 R004853 NULL
which can be loaded with:
create table dbo.temptable
(Account_Number varchar(10),
Parent_Account varchar(10),
Child_Account varchar(10))
insert into dbo.temptable
values
('R003247','R000355','R000002'),
('R000355',NULL,'R003247'),
('R000002','R003247',NULL),
('R004853',NULL,'R028636'),
('R004853',NULL,'R028638'),
('R004853',NULL,'R028637'),
('R028636','R004853',NULL),
('R028638','R004853',NULL),
('R028637','R004853',NULL)
This table denotes splits and reassignments of account numbers. It has to do with the tracking of splits and combinations of land parcels.
The first three lines, as shown in the table above, should be grouped together, because it goes from R000355
--> R003247
--> R000002
The last 6, as shown in the table above, should also be grouped, as it shows R004853
being split into three R028636
,R028637
,R028638
.
I have tried many variation of something like this:
SELECT CE.*,TT.ID
FROM dbo.temptable CE
INNER JOIN
(
SELECT ACCOUNT_NUMBER,ROW_NUMBER() OVER (ORDER BY ACCOUNT_NUMBER) AS ID
FROM(
SELECT DISTINCT ACCOUNT_NUMBER FROM dbo.temptable where Child_Account is not null)AA
)TT
ON TT.ACCOUNT_NUMBER = CE.Account_Number OR TT.Account_Number = CE.Child_Account
Which yeilded:
Account_Number Parent_Account Child_Account ID
R000355 NULL R003247 1
R003247 R000355 R000002 2
R000355 NULL R003247 2
R004853 NULL R028636 3
R004853 NULL R028638 3
R004853 NULL R028637 3
When what I really need is:
Account_Number Parent_Account Child_Account ID
R000355 NULL R003247 1
R003247 R000355 R000002 1
R000002 R003247 NULL 1
R004853 NULL R028636 2
R004853 NULL R028638 2
R004853 NULL R028637 2
R028636 R004853 NULL 2
R028638 R004853 NULL 2
R028637 R004853 NULL 2
Nothing scary about this question, once you jump in and give yourself carpal tunnel from typing too much on a cell phone. This is just a slightly modified standard recursive hierarchical query problem. Of note, the join condition in the recursion is that the current account number is some parent's child account. As for the numbering, we just use DENSE_RANK
over the top level parents.
WITH cte AS (
SELECT m.*, DENSE_RANK() OVER (ORDER BY m.Account_Number) AS pos
FROM temptable m
WHERE Parent_Account IS NULL
UNION ALL
SELECT m.*, cte.pos
FROM temptable m
INNER JOIN cte
ON m.Account_Number = cte.Child_Account
)
SELECT *
FROM cte
ORDER BY pos;
Note: I give massive credit to the brilliant accepted answer here, written by @Quassnoi.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With