SQL Server 2008 R2: ROW_NUMBER()

Question

I have the following data:

Example:

create table vdata
(
cola varchar(10),
colb varchar(10)
 );

insert into vdata values('A','B'),('B','C'),('A','X'),('Z','L'),
('N','M'),('O','P'),('R','Z'),('X','Y'),('T','N'),('F','E'),('G','H');

Looks like:

select * from vdata;

cola   colb
--------------
A       B
B       C
A       X
Z       L
N       M
O       P  
R       Z
X       Y
T       N
F       E
G       H

Note: Now I want to show only that data in which cola have to present in colb OR colb have to present in cola and I want to add the column to show the row number of each record according to the values present in the next records.

Expected Result:

RowNumbers  cola   colb
------------------------
1            A      B
1            A      X
1            B      C
2            N      M
3            R      Z
2            T      N
1            X      Y
3            Z      L    

Explanation of above result: The first row has rownumber 1 at start and again assign to next two records because any of column(cola or colb) has values A or B so. The rownumber 2 given to fouth record because of it does not have any of values A or B and the same way process will continued. And note that record 7 has X and Y its rownumber is 1 because of it relates to A at record 2nd in the table that is A and X.

My bad try:

SELECT
  row_number() over(PARTITION BY cola order by cola) RowNumbers,
  cola,
  colb
FROM vdata
where cola in (select colb from vdata)
or colb in (select cola from vdata)
ORDER BY    cola,colb

Answer 1

Another way...

create table #vdata
(
cola varchar(10),
colb varchar(10)
 );

insert into #vdata values('A','B'),('B','C'),('A','X'),('Z','L'),
('N','M'),('O','P'),('R','Z'),('X','Y'),('T','N'),('F','E'),('G','H');



with src as
(
    select row_number() over(order by cola, colb) id, cola, colb
    from #vdata
)
, [matched] as
(
    select s1.*
    , (select min(s2.id) from src s2 where (s1.cola = s2.cola or s1.colb = s2.cola or s1.cola = s2.colb or s1.colb = s2.colb)) id2
    from src s1
)
, [result] as
(
    select m1.*
    , (select m2.id2 from [matched] m2 where m2.id = m1.id2) [id3]
    from [matched] m1
)
select cola, colb, dense_rank() over(order by id3) [rank]
from [result]
order by id

Answer 2

You need some kind of id in the table to establish ordering

create table #vdata
(
id int identity(1,1),
cola varchar(10),
colb varchar(10)
 );

insert into #vdata values('A','B'),('B','C'),('A','X'),('Z','L'),
('N','M'),('O','P'),('R','Z'),('X','Y'),('T','N'),('F','E'),('G','H');


WITH Roots AS (
    SELECT 1 AS level
         ,id
         ,id AS topid
         ,cola
         ,colb
    FROM #vdata
  UNION ALL
    SELECT level + 1 AS level
          ,#vdata.id
          ,Roots.topid
          ,#vdata.cola
          ,#vdata.colb
    FROM Roots
         INNER JOIN #vdata
             ON (#vdata.id > Roots.id)
                AND (#vdata.cola = Roots.cola
                     OR #vdata.cola = Roots.colb
                     OR #vdata.colb = Roots.cola
                     OR #vdata.colb = Roots.colb)
)
, MaxLevel AS (
    SELECT id 
          ,topid
          ,DENSE_RANK() OVER (ORDER BY topid) AS RowNumbers
          ,cola
          ,colb
    FROM Roots
    WHERE level = (SELECT MAX(level) FROM Roots AS InnerRoots WHERE InnerRoots.id = Roots.id)
)
SELECT RowNumbers
      ,cola
      ,colb
FROM MaxLevel
ORDER BY RowNumbers