sql - Compare data in same table oracle

Question

I need to compare data between two different ids in the same table.

Eg) Id 123 can have items a,b,c
Id 234 can have items x,a,b
And id 789 can have items a,b,c.

My sql should be able to say that Id 123 and Id 789 are the same based on the items column , or say that Id 123 or id 234 are different based on item column.

I guess we can do this with self join in oracle , but I am not sure how .

Correction— Items a, b , c are in 3 different records for I’d 123 or for I’d 789.

question from:https://stackoverflow.com/questions/65941434/compare-data-in-same-table-oracle

Answer 1

You can use cte and join each record with all other records for comparision as follows:

With cte as
(Select id, listagg(item,',') within group (order by item) as items
   From your_table t
  Group by id)
Select t1.id, t2.id,
       Case when t1.items = t2.items then 'match' else 'do not match' end as res_
 From cte t1 join cte t2 on t1.id < t2.id