asked on

placing the value of a row in the column - Oracle query

Hi,

I have tables:
person
person_id
person_name
person_age
sample data: 12345, john, 32

element_entry
element_entry_id
element_name
person_id
sample data: 95, XX, 12345
sample data: 50, color, 12345

element_entry_value
element_entry_id
input_value_id
input_name
input_value
sample data:95, 1111, AM ,1000
sample data:95, 1112, PM ,2000

sample data:50, 1110, black ,3000
sample data:50, 1110, white ,4000

as you can see table element_entry and element_entry_value are joined using element_entry_id.

and element_entry is joined to person using person_id

I would like to write a query to return the following:

if input_name is AM then put corresponding input_value (1000) in column AM and if input_name = White
then put corresponding input_value (4000) in column white.

person_id person_name person_age AM White
12345 john 32 1000 4000

there should be only one record per person in the result set.
and there are many people in the person table.

thanks in advance

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Bullock

select p.person_id, p.person_name, p.person_age,
(select eev.input_value from element_entry_value eev, element_entry ee
where eev.element_entry_id=ee.element_entry_id and ee.person_id=p.person_id
and input_name='AM') as AM,
(select eev.input_value from element_entry_value eev, element_entry ee
where eev.element_entry_id=ee.element_entry_id and ee.person_id=p.person_id
and input_name='white') as WHITE
from person p;

PortletPaul

Using "correlated subqueries" in the select clause requires multiple passes of the tables, and you run the risk of returning more than one row in each (which would crash the query).

Using a subquery that groups by person only requires a single pass of the tables and gives a single value per person.

Devinder Singh Virdi

Step by step how to perform this. Example is not focus on your question, but general guidance
1. Generate 50 numbers in single column.
Select rownum from dual connect by level <= 50;
Select mod(rownum,10) from dual connect by level <= 50;

2. use Decode to create columns with interesting values in each.
select decode(mod(rownum,10), 1, rownum) one,
decode(mod(rownum,10), 2, rownum) two,
decode(mod(rownum,10), 3, rownum) three,
decode(mod(rownum,10), 4, rownum) four,
decode(mod(rownum,10), 5, rownum) five,
decode(mod(rownum,10), 6, rownum) six,
decode(mod(rownum,10), 7, rownum) seven,
decode(mod(rownum,10), 8, rownum) eight,
decode(mod(rownum,10), 9, rownum) nine
from dual connect by level <=50;
3. Since each row contains value in column out of nine columns, we need to group values based on some common criteria, therefore adding another virtual column.
select trunc(rownum/10,0) common,
decode(mod(rownum,10), 1, rownum) one,
decode(mod(rownum,10), 2, rownum) two,
decode(mod(rownum,10), 3, rownum) three,
decode(mod(rownum,10), 4, rownum) four,
decode(mod(rownum,10), 5, rownum) five,
decode(mod(rownum,10), 6, rownum) six,
decode(mod(rownum,10), 7, rownum) seven,
decode(mod(rownum,10), 8, rownum) eight,
decode(mod(rownum,10), 9, rownum) nine
from dual connect by level <=50;
4. Based on newly added column, you can make grouping and find maximum out of that group.
select common, max(one), max(two), max(three), max(four), max(five), max(six), max(seven),
max(eight), max(nine) from
(
select trunc(rownum/10,0) common,
decode(mod(rownum,10), 1, rownum) one,
decode(mod(rownum,10), 2, rownum) two,
decode(mod(rownum,10), 3, rownum) three,
decode(mod(rownum,10), 4, rownum) four,
decode(mod(rownum,10), 5, rownum) five,
decode(mod(rownum,10), 6, rownum) six,
decode(mod(rownum,10), 7, rownum) seven,
decode(mod(rownum,10), 8, rownum) eight,
decode(mod(rownum,10), 9, rownum) nine
from dual connect by level <=50
) group by common
order by 1;

Please excuse me for any type.

Sh M

ASKER

thanks