CODEWITHSUNDEEP
sqldatabaseoracle11gpivotpivot-table
shreeya nagar
shreeya nagar

Reputation: 1

display column names in Oracle using pivot

I'm trying to display the ename in 4 columns namely MANAGER ,SALESMAN, ANALYST, CLERK in SCOTT schema in oracle 11g where only the names of ones who are managers get displayed in 1st column, salesmans in 2nd and so on.

So far I have done this:

select * 
  from (select job,
               ename 
          from emp) 
 pivot (listagg(ename,',') within group(order by ename) 
   for job in ('MANAGER','CLERK','ANALYST','SALESMAN'))

which gives me the desired result but I want in different rows and not multiple values in a single column's value

Anyone can help me display the column names in new columns of PIVOT because it only displays the aggregate functions which typically return a single row where i want multiple rows.

Thanks a lot in advance.

Upvotes: 0

Views: 757

Answers (1)

lsalamon
lsalamon

Reputation: 858

If you don't want to summarize it, I would suggest to use decode:

select decode(job,'MANAGER',ename) Manager, 
       decode(job,'CLERK',ename) CLERK, 
       decode(job,'CLERK',ename) CLERK,
       decode(job,'SALESMAN',ename) SALESMAN
from emp;

Upvotes: 0

Related Questions