SQL : recursive looping

Question

Table is as follows:

I'm trying to get the result set to have groups of all person that intersects from the table, hence creating following groups in result set from attached table.

Person1, Person2, Person3, Person7, Person8
Person5, Person6, Person9

So far I have following query, but can't seem to get the results intersected on a table of rows and outputted as 1 column.

DECLARE @r VARCHAR(MAX), @n INT, @i INT 
SELECT @i = 1,
       @r = 'SELECT BOX, ' + CHAR(13), 
       @n = (SELECT TOP 1 COUNT( USERS ) 
                   FROM EXCHANGE 
                  GROUP BY BOX 
                  ORDER BY COUNT( USERS ) DESC ) ;      
WHILE @i <= @n BEGIN 
           SET @r = @r + 
           CASE WHEN @i = 1  
                THEN 'MAX( CASE Seq WHEN ' + CAST( @i AS VARCHAR ) + ' 
                                 THEN USERS 
                                            ELSE SPACE(0) END ) + ' + CHAR(13) 
           WHEN @i = @n 
             THEN 'MAX( CASE Seq WHEN ' + CAST( @i AS VARCHAR ) + ' 
                                 THEN '', '' + USERS 
                                 ELSE SPACE(0) END ) ' + CHAR(13) 
             ELSE 'MAX( CASE Seq WHEN ' + CAST( @i AS VARCHAR ) + ' 
                                 THEN '', '' + USERS 
                                 ELSE SPACE(0) END ) + ' + CHAR(13)  
           END ;
           SET @i = @i + 1 ;
END 
SET @r = @r + ' 
    FROM ( SELECT BOX, USERS, 
                  ROW_NUMBER() OVER ( PARTITION BY BOX ORDER BY USERS )
             FROM EXCHANGE p ) D ( BOX, USERS, Seq ) 
           GROUP BY BOX;' 
EXEC( @r ) ;

Answer 1

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace RecusriveGroup
{
    public class FinalResult
    {
        public string GroupName { get; set; }
        public string BoxName { get; set; }
        public string UserName { get; set; }
    }
    class Program
    {
        static void Main(string[] args)
        {
            using(var con = new SqlConnection("Data Source=SQLServer;Initial Catalog=TESTDB;Integrated Security=SSPI"))
            {
                con.Open();
                var cmd = new SqlCommand("select distinct Box from Exchange");
                cmd.Connection = con;
                var adapter = new SqlDataAdapter(cmd);
                DataSet dsResult = new DataSet();
                adapter.Fill(dsResult);
                var finalResult = new List<FinalResult>();
                var groupId = 0;
                foreach (DataRow row in dsResult.Tables[0].Rows)
                {
                    if(finalResult.Any(f => f.BoxName.Equals(row["Box"])))
                    {
                        continue;
                    }
                    groupId++;
                    RecursiveCall("Group" + groupId, row["Box"].ToString(), "", con, finalResult);
                }
                foreach(var result in finalResult)
                {
                    var cmd1 = new SqlCommand("INSERT INTO FinalResult(Box, [User], [Group]) VALUES(@Box, @User, @Group)", con);
                    cmd1.Parameters.AddWithValue("@Box", result.BoxName);
                    cmd1.Parameters.AddWithValue("@User", result.UserName);
                    cmd1.Parameters.AddWithValue("@Group", result.GroupName);
                    cmd1.ExecuteNonQuery();
                }
            }
            Console.ReadLine();
        }
        private static void RecursiveCall(string groupName, string boxName, string userName, SqlConnection sqlConnection, List<FinalResult> finalResult)
        {
            DataSet dsResult = new DataSet();
            if (!string.IsNullOrEmpty(boxName) && !string.IsNullOrEmpty(userName))
            {
                var cmd = new SqlCommand("select Box, Users from Exchange WHERE Box = @BoxName OR Users = @UserName");
                cmd.Parameters.AddWithValue("@BoxName", boxName);
                cmd.Parameters.AddWithValue("@UserName", userName);
                cmd.Connection = sqlConnection;
                var adapter = new SqlDataAdapter(cmd);
                adapter.Fill(dsResult);
            }
            else if(!string.IsNullOrEmpty(boxName))
            {
                var cmd = new SqlCommand("select Box, Users from Exchange WHERE Box = @BoxName");
                cmd.Parameters.AddWithValue("@BoxName", boxName);
                cmd.Connection = sqlConnection;
                var adapter = new SqlDataAdapter(cmd);
                adapter.Fill(dsResult);
            } 
            else
            {
                var cmd = new SqlCommand("select Box, Users from Exchange WHERE Users = @UserName");
                cmd.Parameters.AddWithValue("@UserName", userName);
                cmd.Connection = sqlConnection;
                var adapter = new SqlDataAdapter(cmd);
                adapter.Fill(dsResult);
            }
            foreach (DataRow row in dsResult.Tables[0].Rows)
            {
                if (finalResult.Any(f => f.BoxName.Equals(row["Box"].ToString()) && f.UserName.Equals(row["Users"].ToString())))
                {
                    continue;
                }
                finalResult.Add(new FinalResult() { GroupName = groupName, BoxName = row["Box"].ToString(), UserName = row["Users"].ToString() });
                RecursiveCall(groupName, row["Box"].ToString(), row["Users"].ToString(), sqlConnection, finalResult);
            }
        }
    }
}

Answer 2

This type of graph walking is a pain in SQL Server -- you have cycles. The problem is avoiding cycles. Because SQL Server doesn't have very good data types, you need to store the visited nodes as strings.

You can do all this in a recursive CTE. The idea is to follow all paths from a node without repeating any node. Keep the minimum node visited. Voila! That specifies the path:

with cte as (
      select box, users,
             convert(varchar(max), concat(',', box, ',', users, ',')) as path,
             (case when box < users then box else users end) as min_node
      from exchange
      union all
      select cte.box, e.users,
             concat(cte.path, e.users, ','),
             (case when min_node < e.users then min_node else e.users end)
      from cte join
           exchange e
           on e.box = cte.users
      where path not like '%,' + e.users + ',%'
     )
select cte.box, min(cte.users), min(cte.path), min(cte.min_node) as grouping
from cte
group by cte.box;

Here is a db<>fiddle.

This assumes that the edges are symmetric, so if you have (a, b), you also have (b, a).

If this is not the case, it is easy to add a CTE that makes this the case:

select box, users
from exchange
union   -- on purpose to remove duplicates
select users, box
from exchange;