oracle-行转列

 

 

--范例1

select 10 deptno_case10,

       (select distinct case deptno

                          when 10 then

                           listagg(empno, ',') within group(order by ename)

                          else

                           null

                        end deptno

          from emp

         where deptno = 10

         group by deptno) name_case1,

       20 deptno_case20,

       (select distinct case deptno

                          when 20 then

                           listagg(empno, ',') within group(order by ename)

                          else

                           null

                        end deptno

          from emp

         where deptno = 20

         group by deptno) name_case2,

       30 deptno_case30,

       (select distinct case deptno

                          when 30 then

                           listagg(empno, ',') within group(order by ename)

                          else

                           null

                        end deptno

          from emp

         where deptno = 30

         group by deptno) name_case3

  from emp

 

 where rownum < 2

 group by deptno;

 

 

select deptno,listagg(empno,',') within group (order by ename) ename123 from emp group by deptno;

 

 

 

 

 

--范例2

select deptno,listagg(empno,',') within group (order by ename) ename123 from emp group by deptno;