Oracle PL/SQL : Return 1 row from an outer join

Question

I have the following two tables -

Table1

Name Age City

A 21 Delhi

B 23 Mumbai

C 35 Pune

Table2

Name Attribute Attribute_value

A Phone 999999999

A Passport A12345

A Location China

A Skills Developer

B Phone 8888888888

B Skills Tester

Now I want to create table 3 where I get the following details -

Table3

Name Age City Phone Passport Location Skills

Note- the attribute_values should come under the Phone, passport, Location and Skills headings. There should be a single row per 'Name' in table 3.

[Assuming that there are only 4 distinct values in the attribute column in Table2 and for the Name where a certain attribute can not be found, the attribute_value can be assumed as NULL]

So basically want to join and pivot. The solution from Utsav should work. — Josh Gilfillan
– Josh Gilfillan, Commented May 5, 2017 at 5:40
I used simple left join.. and got 4 rows corresponding to 1 name..... — Shruti Joshi
– Shruti Joshi, Commented May 5, 2017 at 11:24

Utsav · Accepted Answer · 2017-05-05 05:56:29Z

1

If there are only 4 attributes, then you can use something like below.

Note: I am assuming that this is a test scenario you are working on. In actual database, name is not a good idea for a key, so you cannot base your processing based on name

select t1.*, t2.*
from
    table1 t1 
left join 
    ( select name,
    max(case when attribute = 'Phone' 
        then attribute_value end) as Phone ,
    max(case when attribute = 'Passport' 
        then attribute_value end) as Passport, 
    max(case when attribute = 'Location' 
        then attribute_value end) as Location,
    max(case when attribute = 'Skills' 
        then attribute_value end) as Skills
     from table2
     group by name
     ) t2 
on t1.name=t2.name

Left join is just to return null for name which are not in table2. If you dont want these, then use inner join.

edited May 5, 2017 at 5:56

answered May 5, 2017 at 5:29

Utsav

8,1632 gold badges22 silver badges40 bronze badges

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

1 Comment

Shruti Joshi Over a year ago

yes, the key is not name. It is an id but the table structure is complex to explain here.

Collectives™ on Stack Overflow

Oracle PL/SQL : Return 1 row from an outer join

Table1

Table2

Table3

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Table1

Table2

Table3

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related