Abhijith Gururaj Abhijith Gururaj - 4 months ago 9
SQL Question

Find duplicate values among multiple columns across different rows

I have a dataset with multiple columns that look similar to this:

ID1 ID2 ID3 ID4
Blue Grey Fuchsia Green
Black Blue Orange Blue
Green Green Yellow Pink
Pink Yellow NA Orange


What I want to do is count how many times each value is duplicated across the four columns. For example, this is what I'd like to get back from the above:

ID Replicates
Blue 3
Black 1
Green 3
Pink 2
Grey 1
Yellow 2
Fuchsia 1
Orange 2


I'd also like to be able to ask which ID value is present in the data set at frequency >2. So the expected result would be: Green and Blue.

Any thoughts on how to do this in Oracle? Thanks!

Answer
select c, count(*)
from
(
select ID1 as c from tablename
union all
select ID2 as c from tablename
union all
select ID3 as c from tablename
union all
select ID4 as c from tablename
)
group by c

Add HAVING count(*) > 2 at the end to get only Green and Blue.