CODEWITHSUNDEEP
sqloracle-databasepivotaggregate-functions
Pradeep Charan
Pradeep Charan

Reputation: 683

Oracle merge two rows as single row with more columns

I have two tables Employee, Employeerows. I have to pull the employee records who has role 2 or 3. I have the query below.

SELECT
    E.ID,
    E.NAME,
    ER.PHONE,
    ER.ADDRESS,
    ER.ROLE
    
FROM
 EMPLOYEE E LEFT JOIN EMPLOYEEROWS ER ON E.ID = ER.ID WHERE ER.ROLE_ID IN (2,3)

This returns either 1 or 2 records for each employee

ID      NAME        PHONE       ADDRESS     ROLE
1       ABC         9898989     ABC NJ       2
1       ABC         7878787     ABC XJ       3
2       DEF         7898765     DEF NJ       2

But I have to merge two records into one for that employee with phone number and address as separate columns if the employee has 2 records. My result should like this.

ID      NAME        PHONE       ALT_PHONE     ADDRESS      ALT_ADDESS   
1       ABC         9898989     7878787        ABC NJ       ABC XJ          
2       DEF         7898765                    DEF NJ

Please help me with this.

Upvotes: 0

Views: 47

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can use conditional aggregation. But the left join seems superfluous. You only want to use the left join if you want "employees" that have no rows.

It appears from your data that "2" corresponds to the primary address and "3" to the alternate:

select e.id, e.name,
       max(case when er.role_id = 2 then er.phone end) as phone,
       max(case when er.role_id = 3 then er.phone end) as alt_phone,
       max(case when er.role_id = 2 then er.address end) as address,
       max(case when er.role_id = 3 then er.address end) as alt_address
from employee e join
     employeerows er
     on e.id = er.id
where er.role_id in (2, 3)
group by e.id, e.name;

However, if "2" could be missing, then you would phrase this as:

select e.id, e.name,
       max(case when seqnum = 2 then er.phone end) as phone,
       max(case when seqnum = 3 then er.phone end) as alt_phone,
       max(case when seqnum = 2 then er.address end) as address,
       max(case when seqnum = 3 then er.address end) as alt_address
from employee e join
     (select er.*,
             row_number() over (partition by er.id order by er.role_id) as seqnum
      from employeerows er
      where er.role_id in (2, 3)
     ) er
     on e.id = er.id
group by e.id, e.name;

Upvotes: 2

Popeye
Popeye

Reputation: 35900

You can use conditional aggregation. But your query is not doing LEFT OUTER JOIN but it is INNER JOIN as you have used the er.role_id in (2,3) in WHERE clause.

Use the following aggreagation technique to fetch the desired result:

SELECT
    E.ID,
    E.NAME,
    MIN(ER.PHONE),
    CASE WHEN MIN(ER.PHONE) <> MAX(ER.PHONE) THEN MAX(ER.PHONE) END AS ALT_PHONE,
    MIN(ER.ADDRESS),
    CASE WHEN MIN(ER.ADDRESS) <> MAX(ER.ADDRESS) THEN MAX(ER.ADDRESS) END AS ALT_ADDRESS
FROM EMPLOYEE E 
LEFT JOIN EMPLOYEEROWS ER ON E.ID = ER.ID 
                   AND ER.ROLE_ID IN (2,3) -- added it in the join condition
GROUP BY E.ID, E.NAME;

Upvotes: 1

GMB
GMB

Reputation: 222432

You can pivot with conditional aggregation:

select e.id, e.name,
    max(case when er.role_id = 2 then er.phone   end) as phone,
    max(case when er.role_id = 3 then er.phone   end) as alt_phone,
    max(case when er.role_id = 2 then er.address end) as address,
    max(case when er.role_id = 3 then er.address end) as alt_address
from employee e 
left join employeerows er on e.id = er.id where er.role_id in (2,3)
group by e.id, e.name

Upvotes: 1

Related Questions