Question: I have following table in T-SQL(there are other columns too but no identity column or primary key column):

```
    Oid Cid
    1    a
    1    b
    2    f
    3    c
    4    f
    5    a
    5    b
    6    f
    6    g
    7    f

```

So in above example I would like to highlight that following Oid are duplicate when looking at Cid column values as "PAIRS":

```
Oid:
1 (1 matches Oid: 5)
2 (2 matches Oid: 4 and 7)

```

Please NOTE that Oid 2 match did not include Oid 6, since the pair of 6 has letter 'G' as well.

Is it possible to create a query without using While loop to highlight the "Oid" like above? along with how many other matches count exist in database? 
I am trying to find the patterns within the dataset relating to these two columns. Thank you in Advance.

Answer: 
Here is a worked example - see comments for explanation:

```
--First set up your data in a temp table
declare @oidcid table (Oid int, Cid char(1));
insert into @oidcid values
(1,'a'),
(1,'b'),
(2,'f'),
(3,'c'),
(4,'f'),
(5,'a'),
(5,'b'),
(6,'f'),
(6,'g'),
(7,'f');

--This cte gets a table with all of the cids in order, for each oid
with cte as (
    select distinct Oid, (select Cid + ',' from @oidcid i2 
                          where i2.Oid = i.Oid order by Cid 
                          for xml path('')) Cids
    from @oidcid i
)
select Oid, cte.Cids 
from cte 
inner join ( 
    -- Here we get just the lists of cids that appear more than once
    select Cids, Count(Oid) as OidCount
    from cte group by Cids
    having Count(Oid) > 1 ) as gcte on cte.Cids = gcte.Cids
-- And when we list them, we are showing the oids with duplicate cids next to each other
Order by cte.Cids

```
```
select o1.Cid, o1.Oid, o2.Oid 
     , count(*) + 1 over (partition by o1.Cid) as [cnt]
from table o1 
join table o2
  on o1.Cid = o2.Cid 
 and o1.Oid < o2.Oid 
order by o1.Cid, o1.Oid, o2.Oid 

```