Ytukyg

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   ¦ 

+----------------------+

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;