CODEWITHSUNDEEP
sqldatabaseoracle-database
Rajiv A
Rajiv A

Reputation: 943

Connect By hierarchical query oracle

I have the following output from a query. I want to get one column with all the distinct values from 3 columns in one query.I am not able to decide if it can be achieved by connect by or some other function.

Dad     Mom    Child
------  ----   -----
D006    M026    LA019
D006    M026    NP082
D005    M048    NG073

Expected output:

D006
D005
M026
M048
LA019
NP082
NG073

Upvotes: 0

Views: 69

Answers (2)

Alex Poole
Alex Poole

Reputation: 191560

A union with a CTE is simpler, but if you're on 11g you could unpivot the columns:

select distinct person
from (
  select *
  from (
    -- this is your real big query
    select 'D006' as dad, 'M026' as mom, 'LA019' as child from dual
    union all select 'D006', 'M026', 'NP082' from dual
    union all select 'D005', 'M048', 'NG073' from dual
  )
  unpivot (person for anyone IN (dad AS 'DAD', mom AS 'MOM', child AS 'CHILD'))
);

PERSON
------
D006   
NP082  
M026   
LA019  
M048   
D005   
NG073  

 7 rows selected

The unpivot operation adds a new column indicating which of the original columns the value came from; since you aren't apparently interested in that it can be discarded, but that has to be done in an outer query.

SQL Fiddle demo showing the inner query with the extra column added by the unpivot, and the same thing wrapped in an outer query to get rid of it and remove duplicates.

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93754

Use Union to combine the different columns into single columns which will also avoid duplicates

SELECT Dad FROM yourtable
UNION
SELECT Mom FROM yourtable
UNION
SELECT Child FROM yourtable

Update : 

WITH cte
     AS (SELECT Dad,Mom,Child
         FROM   bigquery) 
SELECT Dad FROM cte
UNION
SELECT Mom FROM cte
UNION
SELECT Child FROM cte;

Upvotes: 1

Related Questions