Printable View
Oflate many of the interviews, one of the queries is getting very popular which is to display the columns as rows. Ex: 10 sales mum 20 admin chn 30 fincon del out put : 10 20 30 sales admin fincon mum chn del.. Could any tell us whether there is any oracle built in function to do this? what is the easiest way to achieve this.
create table test(empno number,ename varchar2(25)); <br>
insert into test values(10,'sales');
insert into test values(20,'admin');
Select max(case column# when 1 then a end) -- first column <br> -- if 2nd column not null, append after comma <br> || <br> case when <br> max(case column# when 2 then a end) <br> is not null then ','|| <br> max(case column# when 2 then a end) <br> end <br> -- if 3rd column not null, append after comma <br> || <br> case when <br> max(case column# when 3 then a end) <br> is not null then ','|| <br> max(case column# when 3 then a end) <br> end || <br> max(case column# when 1 then b end) -- first column <br> -- if 2nd column not null, append after comma <br> || <br> case when <br> max(case column# when 2 then b end) <br> is not null then ','|| <br> max(case column# when 2 then b end) <br> end <br> -- if 3rd column not null, append after comma <br> || <br> case when <br> max(case column# when 3 then b end) <br> is not null then ','|| <br> max(case column# when 3 then b end) <br> end as details <br> from ( select empno a, ename b <br> , row_number() over <br> (order by ename ) column# <br> from test ) <br> --group by a
--order by a
drop table test;
you can also try the following sample example
[code]
select max(case deptno when 10 then ename end) d10,
max(case deptno when 20 then ename end) d20,
max(case deptno when 30 then ename end) d30,
max(case job when 'CLERK' then ename end) clerks,
max(case job when 'MANAGER' then ename end) mgrs,
max(case job when 'PRESIDENT' then ename end) prez,
max(case job when 'ANALYST' then ename end) anals,
max(case job when 'SALESMAN' then ename end) sales
from (
select deptno, job, ename,
row_number()over(partition by deptno order by empno) rn
from emp
) x
group by rn
[/code]