CODEWITHSUNDEEP
sqloracle-database
Uday
Uday

Reputation: 1484

How to retrieve columns data which are distinct

I need to retrieve columns of data which are having distinct values

For Ex:

Location FoodType Gender Alive
India    Veg      M      Y
India    NonVeg   M      N

I need a query to list rows of data which are having distinct values Expected Output Is:

FoodType Alive
Veg      Y
NonVeg   N

Distinct and Unique may not work as i dont know what column will have distinct values.

Upvotes: 0

Views: 70

Answers (2)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

If you need a list then you could use unpivot, count distinct values, and optionally aggregate them together with listagg():

with t(Location, FoodType, Gender, Alive) as (
    select 'India', 'Veg',    'M', 'Y' from dual union all
    select 'India', 'NonVeg', 'M', 'N' from dual )

-- end of test data, the exact query starts here:

select col, listagg(val, ', ') within group (order by val) list
  from (
    select col, val, count(distinct val) over (partition by col) cnt
      from t
      unpivot (val for col in (Location, FoodType, Gender, Alive)))
  where cnt > 1
  group by col

We have this:

COL       LIST
--------  -------------
ALIVE     N, Y
FOODTYPE  NonVeg, Veg

If you insist on output in separate columns You need dynamic SQL to produce list of non distinct columns to pivot data again.

If there are null values in table and non nulls and you want to show such pairs use unpivot with include nulls and then nvl().

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

This is too long for a comment.

A SQL query has a fixed set of columns. You cannot specify a query that has a variable number -- although you can do so using dynamic SQL.

You could get a list of distinct values within columns:

select count(distinct Location),
       count(distinct FoodType),
       count(distinct Gender),
       count(distinct Alive)
from t;

However, this doesn't return the values in the columns.

Upvotes: 0

Related Questions