Thursday, December 13, 2007

Department wise employees in Oracle SQL

select dept,
max(decode(my_seq,1,emp_name)) || ' ' ||
max(decode(my_seq,2,emp_name)) || ' ' ||
max(decode(my_seq,3,emp_name)) || ' ' ||
max(decode(my_seq,4,emp_name)) || ' ' ||
max(decode(my_seq,5,emp_name)) emp_list
from (
select dept,
emp_name,
row_number() over
(partition by dept order by emp_name) my_seq
from temp_01_tb
)
group by dept
order by 1
;

DEPT EMP_LIST
------- --------------------------------------------
1 Bob David Sally Sam
2 Jane Jennifer Joe

No comments: