SQL Server - manage rows in same table

Question

How to create a new dataset with same values of same table?

Script below shows the sample situation that I have to working on production environment :

IF EXISTS (SELECT 1 FROM DevTest.SYS.OBJECTS WHERE NAME  = 'DevTable') 
     DROP TABLE DevTable ;

CREATE TABLE DevTable 
( 
    Field1 VARCHAR(10),
    Field2 VARCHAR(10)
);

INSERT INTO DevTable
      SELECT 'Martin' , 'Jackson'
      UNION ALL 
      SELECT 'Theresa' , 'Allison'
      UNION ALL 
      SELECT 'Jackson' , 'Brad'
      UNION ALL 
      SELECT 'Beatriz' , 'Lewis'
      UNION ALL 
      SELECT 'Jose' , 'Mary'
      UNION ALL 
      SELECT 'Mary' , 'Allan'
      UNION ALL 
      SELECT 'Brad' , 'Joaquim'
      UNION ALL 
      SELECT 'Joaquim' , 'Lia'
END 

Retrieve data like this:

And I have to get this result:

I was wondering some using hierarchy function in SQL Server 2012 to group rows of same table.

Thanks in advance

Answer 1

As you only have 42 rows of data in the table you can use a recursive CTE to checkout the path for every row in the table in both directions. and keep track of the minimum Field1/Field2 combination found in the path.

Then use that to group rows together as shown below. This assumes that there are no cycles in your data. If there are you will receive an error due to infinite recursion.

WITH R1
     AS (SELECT *,
                CONCAT(Field1, '~', Field2) AS Grp
         FROM   DevTable
         UNION ALL
         SELECT D.*,
                CASE
                  WHEN CONCAT(D.Field1, '~', D.Field2) < Grp THEN CONCAT(D.Field1, '~', D.Field2)
                  ELSE Grp
                END
         FROM   DevTable D
                JOIN R1
                  ON R1.Field2 = D.Field1),
     R2
     AS (SELECT *,
                CONCAT(Field1, '~', Field2) AS Grp
         FROM   DevTable
         UNION ALL
         SELECT D.*,
                CASE
                  WHEN CONCAT(D.Field1, '~', D.Field2) < Grp THEN CONCAT(D.Field1, '~', D.Field2)
                  ELSE Grp
                END
         FROM   DevTable D
                JOIN R2
                  ON D.Field2 = R2.Field1),
     R
     AS (SELECT *
         FROM   R1
         UNION ALL
         SELECT *
         FROM   R2),
     T
     AS (SELECT *,
                ROW_NUMBER()
                  OVER (
                    PARTITION BY Field1, Field2
                    ORDER BY Grp) AS RN
         FROM   R)
SELECT Field1,
       Field2,
       DENSE_RANK()
         OVER (
           ORDER BY Grp)
FROM   T
WHERE  RN = 1