question

I have two columns OrigCustId & AssCustid in one table. I need to match them together and assign one unique value. OrigCustid is assoicated with AssCustid & AssCustid is associated with OrigCustId. I have to group such kind of rows and give them a Unique value. Table OrigCustId AssCustid 1 1 1 2 2 2 2 3 3 3 4 4 4 5 5 5 5 7 6 4 6 6 7 4 7 7 8 8 Final Output Col GID 1 1 2 1 3 1 4 2 5 2 6 2 7 2 8 3 Column has varchar data type. And numbers are random. Above is just an example. I have tried like this but could not get result. WITH RNCTE AS ( SELECT distinct OrigionalCustomerID,AssociatedCustomerID, DENSE_RANK() OVER ( ORDER BY OrigionalCustomerID ASC) rn FROM #T1 with (NOLOCK) where len(OrigionalCustomerID)>0 ), CTE AS ( SELECT OrigionalCustomerID,AssociatedCustomerID,rn FROM RNCTE --WHERE rn = 1 UNION ALL SELECT r.AssociatedCustomerID ,r.OrigionalCustomerID,r.rn FROM CTE c JOIN RNCTE r on c.OrigionalCustomerID = r.OrigionalCustomerID AND c.rn+1 = r.rn ) SELECT distinct OrigionalCustomerID,AssociatedCustomerID,MIN(rn) GINID FROM CTE Group by OrigionalCustomerID, AssociatedCustomerID Order by GINID