We help IT Professionals succeed at work.

Convert rows  into columns firstnames

sam_2012
sam_2012 asked
on
Hi Team,

Need an help in writing a query as below in HR schema in oracle 12c .  I need to convert the rows into columns

Employees table

First_name
department_id
Job_id


Department Id ST_MAN ST_CLERK  SH_CLERK
50                         joseph      vignesh         ratnavel      
50                         christine      sed               govinda
50                         rajesh          rajiv              
50                                              gopal        






Iam trying the below quey , but not able to aggrgate all the names in a column




Select department_id,
max(case when job_id='ST_MAN' then
first_name
end) ST_MAN,
max(case when job_id='ST_CLERK' then
first_name
end) ST_CLERK,
max(case when job_id='SH_CLERK' then
first_name
end) SH_CLERK
from employees
where department_id=50
group by department_id

But iam not getting all the nams in the order as shown in the output. Any help is really appreciated
Comment
Watch Question

Author

Commented:
Help to do the same using decode or case statement.
Mark GeerlingsDatabase Administrator

Commented:
Have you tried using the PIVOT operator?  That may provide functionality close to what you are asking for.
What about "order by"?  Do you want the names listed in a particular order within each job?
When you say "aggregate all the names in a column" do you want multiple names on one line in each column, but comma-separated?  If not,what exactly do you want the output to look like?
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:

Yes, pivot is what you want.  As long as you want the names ordered alphabetically, this produces what I believe is what you want just not in the order you provided.


For job_id, I used 1,2,3 for the titles.


/*
--drop table tab1 purge;
create table tab1( department_id number, Job_id number, First_name varchar2(10));

insert into tab1 values(50,1,'joseph');
insert into tab1 values(50,2,'vignesh');
insert into tab1 values(50,3,'ratnavel');
insert into tab1 values(50,1,'christine');
insert into tab1 values(50,2,'sed');
insert into tab1 values(50,3,'govinda');
insert into tab1 values(50,1,'rajesh');
insert into tab1 values(50,2,'rajiv');
insert into tab1 values(50,2,'gopal');
commit;
*/

select department_id, st_man, st_clerk, sh_clerk
 from (
    select
        department_id,
        first_name,
        job_id,
        row_number() over(partition by job_id order by first_name) rn
    from tab1
)
pivot 
(
   max(first_name)
   for job_id in (1 as st_man,2 as st_clerk,3 sh_clerk)
)
order by rn
;

Author

Commented:
awesome