CODEWITHSUNDEEP
sql-servert-sql
hss
hss

Reputation: 317

TSQL : Find PAIR Sequence in a table

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.

Upvotes: 0

Views: 236

Answers (3)

Owain Esau
Owain Esau

Reputation: 1922

Maybe Like this then:

WITH CTE AS
(
    SELECT Cid, oid
    ,ROW_NUMBER() OVER (PARTITION BY cid ORDER BY cid) AS RN
    ,SUM(1) OVER (PARTITION BY oid) AS maxRow2
    ,SUM(1) OVER (PARTITION BY cid) AS maxRow
    FROM oid
)
SELECT * FROM CTE WHERE maxRow != 1 AND maxRow2 = 1
ORDER BY oid

Upvotes: 0

BeanFrog
BeanFrog

Reputation: 2315

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

Upvotes: 2

paparazzo
paparazzo

Reputation: 45096

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

Upvotes: 0

Related Questions