Creating a linked ID

Question

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

`The first three lines` ... this is meaningless unless you provide an `ORDER BY` clause which generates this order. A SQL table is a bit different than a table in Excel. — Tim Biegeleisen, May 25 '18 at 14:37
@TimBiegeleisen I added `as shown in the table above` is that sufficient for the reader to know that I am referring to the table as posted in the question? — Scott Craner, May 25 '18 at 14:42
This is tricky, I can figure out what you want by your expected output +1. You need a recursive hierarchical query. I don't know how to do this :-( — Tim Biegeleisen, May 25 '18 at 14:51
Might find something helpful here: https://stackoverflow.com/questions/18618999/group-all-related-records-in-many-to-many-relationship-sql-graph-connected-comp — Tab Alleman, May 25 '18 at 14:53
Your table has me scratching my head. It is redundant that you have a row that states the child and another row stating the parent. The main reason you are struggling here is because the data structure is not as well normalized as it could be. You have a many to many relationship all crammed into a single table. — Sean Lange, May 25 '18 at 14:54

Tim Biegeleisen · Accepted Answer · 2018-05-25T15:27:11.130

3

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;

Demo

Note: I give massive credit to the brilliant accepted answer here, written by @Quassnoi.

edited May 25 '18 at 15:27

answered May 25 '18 at 15:20

Tim Biegeleisen

502,043
27
286
360

Thank you, Just shows how much more there is to learn about sql. – Scott Craner May 25 '18 at 15:26
@ScottCraner These questions don't come up often on Stack Overflow, hence it took you a while to get an answer. – Tim Biegeleisen May 25 '18 at 15:28

Creating a linked ID

1 Answers1

Demo