Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, GAJANANA.

Asked: May 10, 2017 - 8:03 am UTC

Last updated: May 10, 2017 - 8:30 am UTC

Version: 11.1.0.6.0

Viewed 1000+ times

You Asked

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.

with LiveSQL Test Case:

and Chris said...

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

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.