how to insert values from multiple rows into a record in oracle?

Question

I have a table A like this:

   id   field      value
----------------------
   1845  name       john
   1845  post       manager
   1845  birth      1980
   1846  name       alex
   1846  post       employee
   1846  birth      1986

i want a result set that look like this:

     id     name     post     birth
----------------------------------------
    1845    john    manager   1980
    1846    alex    employee  1986
.
.
.

is there any way in oracle to do this?

zedfoxus · Accepted Answer · 2015-05-25 04:00:59Z

2

Assuming that ID field will have the same number for a given name, post and birth, the following query might help you.

select 
  distinct 
  id,
  (select value from test where id = a.id and field = 'name') as name,
  (select value from test where id = a.id and field = 'post') as post,
  (select value from test where id = a.id and field = 'birth') as birth
from test a

Note that the above query will not work for the case below where there are 2 names for the same ID:

id   field      value
----------------------
1845  name       john
1845  post       manager
1845  birth      1980
1845  name       alex
1845  post       employee
1845  birth      1986

Example: http://sqlfiddle.com/#!4/ca93f/3

answered May 25, 2015 at 4:00

zedfoxus

37.4k5 gold badges68 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to insert values from multiple rows into a record in oracle?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related