How to display the related records in a single row in Oracle SQL?

Question

I wrote a query joining two tables and I got a below resultset:

SELECT emp.employee_id,
      dept.department_name, 
      dept.department_id                                    
FROM employee emp, 
    department dept                                
WHERE emp.department_id = dept.department_id;

Employee_ID Department  Department_ID
Mark        Sales          D1
Mark        Marketing      D2
Justin      Textiles       D3
Kimberley   (null)        (null)

However, I need to display below output with one new field called 'Status'.Mark can work in both the departments and so the count is "2" and the status will be 'Y' ( displaying of any one record is okay) . Justin works in only one department and count is 1 and status should be 'N'. Kimberley does not work anywhere and count is 0 and status should be 'N'.

Expected output:

Employee_ID  Department  Department_ID  Status
Mark          Sales          D1            Y
Justin        Textiles       D3            N
Kimberley      (null)       (null)         N

Please help.

score 2 · Accepted Answer · answered Dec 05 '19 at 21:52

I understand that you want to display the first department per user, and add a flag that indicates whether the employee belongs to at least one other department.

You can use window functions:

select 
    employee_id,
    department_name,
    department_id
    case when cnt <= 1 then 'N' else 'Y' end status
from (
    select 
        emp.employee_id,
        dept.department_name, 
        dept.department_id,
        row_number() over(partition by emp.employee_id order by dept.department_id) rn,
        count(*) over(partition by emp.employee_id) cnt
    from 
        employee emp
        left join department dept on emp.department_id = dept.department_id
) t
where rn = 1

Side note: always use explicit joins (with the on keyword) instead of old-school, implicit joins (with commas in the from clause), whose syntax is harder to read and maintain.

score 1 · Answer 2 · answered Dec 06 '19 at 03:08

I think this can be easily achieved using group by and keep clause with max as following:

SELECT emp.employee_id,
      Max(dept.department_name) keep (dense_rank first order by dept.department_id) as department_name,
      Max(dept.department_id) keep (dense_rank first order by dept.department_id) as department_id,
      case when count(1) > 1 then 'Y' else 'N' end as status                                   
FROM employee emp 
     LEFT JOIN department dept ON emp.department_id = dept.department_id
GROUP BY emp.employee_id;

Cheers!!

How to display the related records in a single row in Oracle SQL?

2 Answers2