0

I have about 150 names, that are linked together by 160K different id numbers. I'd like to get the distinct combonations of these names. So for example, the names John and Kate can be linked thousands of times by different Ids, but I just want to know that John and Kate are a linked pair. In some cases there are more than 2 names linked. For example, this is what my table currently looks like:

ID| Name|  
 1| Mike|
 1|John |
 2|Kate |
 2|John |
 3|Frank|
 3|Ted  |
 3|Blue |
 4|John |
 4|Mike |
 5|John |
 5|Kate |
 6|John |
 6|Kate |

In this case, we have combinations based on the various ids. We also have redundant combinations; for example id1 and 4, as well as 2, 5, 6. I'd like the output just to give me the distinct combos, regardless of order they appear in the table. The output would look like:

Name1| Name2 |Name3
Mike | John  |
Kate | John  |
Frank| Ted   | Blue

4 would be omitted because its the frist row, and 5, 6 would be omitted since its covered by the second row

user7002207
  • 457
  • 2
  • 8
  • You challenge you are facing is called dynamic pivot. See https://stackoverflow.com/questions/42009404/dynamic-pivot-in-oracle-sql-procedure – SQLRaptor Mar 15 '19 at 17:17

1 Answers1

0

For linked pairs, you would use a self-join:

select distinct t1.name, t2.name
from t t1 join
     t t2
     on t1.id = t2.id and t1.name < t2.name;

I am not clear if that solves your entire problem, though.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks Gordon, it seems to be working if I change select t1.name, to select distinct t1.name; It looks like that's the result, would adding that 'distinct' cause oracle to give me only distinct combos ? – user7002207 Mar 15 '19 at 18:10