Hi Tom,
create table emp_det
( name varchar2(15),
occupation varchar2(15)
);
insert into emp_det values('samantha','doctor');
insert into emp_det values('julia','actor');
insert into emp_det values('maria','actor');
insert into emp_det values('meera','singer');
insert into emp_det values('ashley','professor');
insert into emp_det values('ketty','professor');
insert into emp_det values('jane','actor');
insert into emp_det values('jenny','doctor');
insert into emp_det values('priya','singer');
commit;
i want output as below.
doctor professor singer actor
jenny ashley meera jane
samantha ketty priya julia
null null null maria
Please help me.
You need to do the pivot!
If you want a separate row for each person with a profession, assign a number to each row for the person within the profession. Oracle Database will then implicitly group by this number, splitting them to separate rows:
set null <null>
with rws as (
select e.*,
row_number() over (partition by occupation order by name) rn
from emp_det e
)
select * from rws
pivot (
min(name) for occupation in ('doctor', 'actor', 'singer', 'professor')
);
set null <null>
with rws as (
select e.*,
row_number() over (partition by occupation order by name) rn
from emp_det e
)
select * from rws
pivot (
min(name) for occupation in ('doctor', 'actor', 'singer', 'professor')
);
For more about pivoting, read:
https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot