This discussion is archived
5 Replies Latest reply: Aug 27, 2013 9:19 AM by mrosa001 RSS

Rows to columns

Mindmap Pro
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Hi,

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

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points