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

Question

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

Is there a reason you must use a key/value table? They are a nuisance to work with. Why not just a person table with the columns man, employed and married instead? That would make this a lot easier (`select id, man, married from person` in your case). — Thorsten Kettner, Jun 22 '21 at 10:11
Does this answer your question? [oracle transposing text value rows to columns](https://stackoverflow.com/questions/43764683/oracle-transposing-text-value-rows-to-columns). Or more general [Oracle SQL pivot query](https://stackoverflow.com/questions/4841718/oracle-sql-pivot-query) — astentx, Jun 22 '21 at 10:23

gaborsch · Answer 1 · 2021-06-22T08:23:05.900

1

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.

edited Jun 22 '21 at 08:23

answered Jun 22 '21 at 08:16

gaborsch

15,408
6
37
48

1

Yep, conditional aggreation is the typical way to deal with key/value tables. `ELSE null` is the default and can hence be left out. If you want the results ordered by id, add `` ORDER BY id`. – Thorsten Kettner Jun 22 '21 at 10:09
this is just an example, as i have a data set with many different data code and its corresponding data values for an id. For example, datacode: ic, hp number, address, income, etc. but i want columns of datacode group by id, i am thinking that pivot would do the trick? – Ng Zheng Jie Jun 24 '21 at 02:00
I think both approaches work, try to make a performance comparison - and let me know the result, I'm curious! – gaborsch Jun 24 '21 at 08:09

score 0 · Answer 2 · answered Jun 22 '21 at 15:02

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
  ) 
)
;

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

2 Answers2