CODEWITHSUNDEEP
sqloracle-databasejoin
Shruti Joshi
Shruti Joshi

Reputation: 173

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

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]

Upvotes: 0

Views: 49

Answers (1)

Utsav
Utsav

Reputation: 8103

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.

Upvotes: 1

Related Questions