Parent child Hierarchy based on non unique order number

Question

I have a table containing system and order number column which is not unique.I would like to connect the system based on the order number

+----------------------+  
¦ system       ¦OrderNo¦
¦--------------+-------+ 
¦ system1      ¦ 1     ¦  
¦ system2      ¦ 2     ¦  
¦ system3      ¦ 3     ¦ 
¦ system4      ¦ 4     ¦ 
¦ system1      ¦ 1     ¦ 
¦ system2      ¦ 2     ¦  
¦ system5      ¦ 1     ¦  
¦ system6      ¦ 2     ¦ 
¦ system7      ¦ 1     ¦ 
¦ system8      ¦ 2     ¦ 
+----------------------+

How do I generate a parent child relation based on the OrderNo Column. Where 1-2-3-4 are one set, 1-2,1-2,1-2 the other set

desired Output is as below

+----------------------+  
¦ Parent       ¦Child  ¦
¦--------------+-------+ 
¦ system1      ¦system2¦  
¦ system2      ¦system3¦  
¦ system3      ¦system4¦ 
¦ system4      ¦NULL   ¦ 
¦ system1      ¦system2¦ 
¦ system2      ¦NULL   ¦  
¦ system5      ¦system6¦  
¦ system6      ¦NULL   ¦ 
¦ system7      ¦system8¦ 
¦ system8      ¦NULL   ¦ 
+----------------------+

Are those the only columns you have? If so, impossible with that data; you cannot guarantee the order of your data with the use of an `ORDER BY` clause, and thus you have no way to determine the relationship. — Thom A, Nov 24 '18 at 21:46

Thom A · Accepted Answer · 2018-11-24T22:10:24.643

As I mentioned in my comment, if these are the only columns you have, what you are after isn't achievable. Without some kind of a ascending unique key, you have no way of determining the relationship. If we do add a unique ascending key then we can achieve this.

If you were using SQL Server 2012+ (2008 is out of support, and very close to end of extended support, so upgrading should be a very high priority right now), then you could generate your islands using ROWS BETWEEN and then use LEAD:

CREATE TABLE dbo.SampleTable (ID int IDENTITY(1,1),
                              [system] varchar(8),
                              OrderNo int);
INSERT INTO dbo.SampleTable([System],OrderNo)
VALUES('system1',1),  
      ('system2',2),  
      ('system3',3), 
      ('system4',4), 
      ('system1',1), 
      ('system2',2),  
      ('system5',1),  
      ('system6',2), 
      ('system7',1), 
      ('system8',2); 
GO

WITH Groups AS(
    SELECT ID,
           [System],
           COUNT(CASE WHEN OrderNo = 1 THEN 1 END) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Grp
    FROM dbo.SampleTable)
SELECT [System] AS Parent,
       LEAD([System]) OVER (PARTITION BY Grp ORDER BY ID) AS Child
FROM Groups;
GO

Without access to LEAD and ROWS BETWEEN you'll have to be more creative; and the solution is going to be far slower:

WITH Groups AS(
    SELECT ST.ID,
           ST.[System],
           G.Grp
    FROM dbo.SampleTable ST
         CROSS APPLY (SELECT COUNT(*) AS Grp
                      FROM dbo.SampleTable CA
                      WHERE CA.OrderNo =1
                        AND CA.ID <= ST.ID) G)
SELECT G1.[System] AS Parent,
       G2.[System] AS Child
FROM Groups G1
     LEFT JOIN Groups G2 ON G1.Grp = G2.Grp
                        AND G1.ID = G2.ID - 1;

It works great on sql server 2008, but without LEAD the output is not the same — xoanon, Nov 28 '18 at 12:40

Parent child Hierarchy based on non unique order number

1 Answers1