5 Replies Latest reply: Aug 27, 2013 11:19 AM by mrosa001 RSS

    Rows to columns

    Mindmap
      Hello,

      I am using Oracle 11g R2 SOE

      I need a query to get the result elaborated the following image where each row should show dept name along with the employees working on that dept (Each employee in a separate column e.g EMP1, EMP2, ..... EMPn ).

      [url http://www.theexecutivetowers.info/i/images/pivot.PNG]Please click here


      Best Regards,
      Fateh

      Edited by: Fateh on Jan 20, 2013 3:45 AM
        • 1. Re: Rows to columns
          Solomon Yakobson
          This is called pivoting. Oracle does not provide dynamic pivoting (unless producing XML), therefore you need to know max employee count per department upfront:
          with t as (
                     select  dname,
                             ename,
                             row_number() over(partition by d.deptno order by sal desc) rn
                       from  emp e,
                             dept d
                       where e.deptno = d.deptno
                    )
          select  *
            from  t
            pivot (max(ename) for rn in (1 emp1,2 emp2,3 emp3,4 emp4,5 emp5,6 emp6,7 emp7,8 emp8))
          /
          
          DNAME          EMP1       EMP2       EMP3       EMP4       EMP5       EMP6       EMP7       EMP8
          -------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
          ACCOUNTING     KING       CLARK      MILLER
          RESEARCH       FORD       SCOTT      JONES      ADAMS      SMITH
          SALES          BLAKE      ALLEN      TURNER     WARD       MARTIN     JAMES
          
          SQL>
          SY.
          • 2. Re: Rows to columns
            Frank Kulash
            Hi,

            Also, see {message:id=3527823}
            • 3. Re: Rows to columns
              Mindmap
              Thanks All,
              (unless producing XML)
              You guessed...
              • 4. Re: Rows to columns
                817257
                use case statement , its simple.
                • 5. Re: Rows to columns
                  mrosa001

                  I need a helo to pivot table with variable columns,

                   

                  I have a pivot table :

                   

                  SELECT a.*

                  FROM (SELECT codigo_aluno,nome_aluno , id_curso,dia FROM c_frequencia where dia like '201308%') PIVOT (sum(null)   FOR dia IN ('20130805' ,'20130812','20130819','20130826')) a

                   

                  but I need to run the select with values for dia , getting from a other table :

                   

                  SELECT a.*

                  FROM (SELECT codigo_aluno,nome_aluno , id_curso,dia FROM c_frequencia where dia like '201308%') PIVOT (sum(null)   FOR dia IN (

                  select dia from v_dia_mes )) a

                   

                  thank you