CODEWITHSUNDEEP
sqloracle-database
Ng Zheng Jie
Ng Zheng Jie

Reputation: 1

Is there a Oracle SQL function to return a row with columns based on different different values in one column?

id datacode datavalue
100 man Y
100 employed N
100 married Y
101 man N
101 employed Y
101 married Y

If i had a table like below, is there any function that allows me to have a row that only returns columns the id, man, married where the value of man and married equal its respective datavalue without self joining.

The desired outcome is as follows:

id man married
100 Y Y
101 N Y

Upvotes: 0

Views: 81

Answers (2)

Mahamoutou
Mahamoutou

Reputation: 1555

You could also simply use pivot clause for that purpose like below :

with sample_data(id, datacode, datavalue) as (
select 100, 'man'       , 'Y' from dual union all
select 100, 'employed'  , 'N' from dual union all
select 100, 'married'   , 'Y' from dual union all
select 101, 'man'       , 'N' from dual union all
select 101, 'employed'  , 'Y' from dual union all
select 101, 'married'   , 'Y' from dual 
)
select *
from sample_data t
pivot (
max(DATAVALUE) for DATACODE in (
    'man' as man
--  , 'employed' as employed
  , 'married' as married
  ) 
)
;

Upvotes: 0

gaborsch
gaborsch

Reputation: 15758

Try max(case...) in combination with group by.

select id, 
       max(CASE WHEN datacode = 'man' THEN datavalue ELSE null END) as man,
       max(CASE WHEN datacode = 'married' THEN datavalue ELSE null END) as married
from mytable
group by id;

The aggregate function max() is working on groups, and it will filter out null values - what we create with the case statement for non-matching rows. The result is one row for each id.

Upvotes: 1

Related Questions