Source table :
----------------------------------------
| Employee Name | department | Emp Id |
----------------------------------------
| Sam | Sales | 101 |
----------------------------------------
| Sam | Finance | 101 |
----------------------------------------
| Dirk | marketing | 102 |
----------------------------------------
| Dirk | Research | 102 |
----------------------------------------
Output needed :
------------------------------------------------------
| Employee Name | Emp Id | department1 | department2|
------------------------------------------------------
| Sam | 101 | Sales | Finance |
------------------------------------------------------
| Dirk | 102 | marketing | Research |
------------------------------------------------------
Can you kindly help with what functions or query should i use to get above mentioned output?
It sounds like you want to pivot.
If you want a column for each of the N departments an employee has, then:
- In a subquery assign row numbers for each department/employee, starting at one for each employee
- Take the max department name for each row number in the pivot clause
Which looks something like:
with rws as (
select t.*,
row_number() over (
partition by emp_id
order by department
) rn
from ... t
)
select * from rws
pivot (
max ( department ) dept
for rn in ( 1, 2 )
)
For full details on how to use pivot, see
https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot