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
shmzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
The requirement requires a "pivot" of the entry values from rows into columns, which can be done using this subquery
SELECT
     ee.person_id
    , MAX(case when input_name = 'AM'    then input_value end) as AM
    , MAX(case when input_name = 'white' then input_value end) as white
FROM element_entry_value eev
inner join element_entry ee on eev.element_entry_id = ee.element_entry_id
WHERE (input_name = 'AM' AND input_value = 1000)
   OR (input_name = 'white' AND input_value = 4000)
GROUP BY
     ee.person_id
;

Open in new window

Then this may be linked to your person table, here I have used a LEFT JOIN, but you could use an INNER JOIN depending on your (unstated) need. Like this:
select
      p.person_id
    , p.person_name
    , p.person_age
    , e.AM
    , e.White
from person p
left join (
      SELECT
           ee.person_id
          , MAX(case when input_name = 'AM'    then input_value end) as AM
          , MAX(case when input_name = 'white' then input_value end) as white
      FROM element_entry_value eev
      inner join element_entry ee on eev.element_entry_id = ee.element_entry_id
      WHERE (input_name = 'AM' AND input_value = 1000)
         OR (input_name = 'white' AND input_value = 4000)
      GROUP BY
           ee.person_id
  ) e on p.person_id = e.person_id

Open in new window

Use LEFT JOIN if you need all rows from person table even if they have no matching entry values.

Details:
    CREATE TABLE PERSON
        (PERSON_ID int, PERSON_NAME varchar2(4), PERSON_AGE int)
    ;
    
    INSERT ALL 
        INTO PERSON (PERSON_ID, PERSON_NAME, PERSON_AGE)
             VALUES (12345, 'john', 32)
    SELECT * FROM dual
    ;
    
    CREATE TABLE ELEMENT_ENTRY
        (ELEMENT_ENTRY_ID int, ELEMENT_NAME varchar2(5), PERSON_ID int)
    ;
    
    INSERT ALL 
        INTO ELEMENT_ENTRY (ELEMENT_ENTRY_ID, ELEMENT_NAME, PERSON_ID)
             VALUES (95, 'XX', 12345)
        INTO ELEMENT_ENTRY (ELEMENT_ENTRY_ID, ELEMENT_NAME, PERSON_ID)
             VALUES (50, 'color', 12345)
    SELECT * FROM dual
    ;
    
    
    CREATE TABLE ELEMENT_ENTRY_VALUE
        (ELEMENT_ENTRY_ID int
         , INPUT_VALUE_ID int, INPUT_NAME varchar2(5), INPUT_VALUE int)
    ;
    
    INSERT ALL 
        INTO ELEMENT_ENTRY_VALUE ("ELEMENT_ENTRY_ID", "INPUT_VALUE_ID", "INPUT_NAME", "INPUT_VALUE")
             VALUES (95, 1111, 'AM', 1000)
        INTO ELEMENT_ENTRY_VALUE ("ELEMENT_ENTRY_ID", "INPUT_VALUE_ID", "INPUT_NAME", "INPUT_VALUE")
             VALUES (95, 1112, 'PM', 2000)
        INTO ELEMENT_ENTRY_VALUE ("ELEMENT_ENTRY_ID", "INPUT_VALUE_ID", "INPUT_NAME", "INPUT_VALUE")
             VALUES (50, 1110, 'black', 3000)
        INTO ELEMENT_ENTRY_VALUE ("ELEMENT_ENTRY_ID", "INPUT_VALUE_ID", "INPUT_NAME", "INPUT_VALUE")
             VALUES (50, 1110, 'white', 4000)
    SELECT * FROM dual
    ;
**Query 1**:

    SELECT
         ee.person_id
        , MAX(case when input_name = 'AM'    then input_value end) as AM
        , MAX(case when input_name = 'white' then input_value end) as white
    FROM element_entry_value eev
    inner join element_entry ee on eev.element_entry_id = ee.element_entry_id
    WHERE (input_name = 'AM' AND input_value = 1000)
       OR (input_name = 'white' AND input_value = 4000)
    GROUP BY
         ee.person_id
    

**[Results][2]**:
    | PERSON_ID |   AM | WHITE |
    |-----------|------|-------|
    |     12345 | 1000 |  4000 |
**Query 2**:

    
    
    
    select
          p.person_id
        , p.person_name
        , p.person_age
        , e.AM
        , e.White
    from person p
    left join (
          SELECT
               ee.person_id
              , MAX(case when input_name = 'AM'    then input_value end) as AM
              , MAX(case when input_name = 'white' then input_value end) as white
          FROM element_entry_value eev
          inner join element_entry ee on eev.element_entry_id = ee.element_entry_id
          WHERE (input_name = 'AM' AND input_value = 1000)
             OR (input_name = 'white' AND input_value = 4000)
          GROUP BY
               ee.person_id
      ) e on p.person_id = e.person_id
      
    /*
    person_id person_name person_age   AM        White
    12345     john        32           1000      4000    
    
    
     --input_value (1000) in column AM 
     --and if input_name = White
     */
     

**[Results][3]**:
    | PERSON_ID | PERSON_NAME | PERSON_AGE |   AM | WHITE |
    |-----------|-------------|------------|------|-------|
    |     12345 |        john |         32 | 1000 |  4000 |

  [1]: http://sqlfiddle.com/#!4/756fb/10

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark BullockQA Engineer IIICommented:
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;
0
PortletPaulfreelancerCommented:
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.
0
Devinder Singh VirdiLead Oracle DBA TeamCommented:
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.
0
shmzAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.