2 Replies Latest reply: Feb 26, 2013 12:30 PM by Solomon Yakobson RSS

    how to pivot a query

    claudioaragao
      hi guys. I need "to pivot" a query dynamically ,sorry i don't know if this is right word, but i have the following scenario

      cod conference nr. user qtd

      10 1 tom 8
      10 2 kim 7
      20 1 jos 10
      20 2 joe 11
      20 3 bil 12
      .
      .
      .
      .

      and i need put him on this way...

      cod conference nr. user qtd conference nr. user qtd conference nr. user qtd
      10 1 tom 8 2 kim 7
      20 1 jos 10 2 joe 11 3 bil 12
      .
      .
      .

      its possible..?

      thanks.

      i'm using oracle 10g r2.0.5
        • 1. Re: how to pivot a query
          Frank Kulash
          Hi,

          See the forum FAQ {message:id=9360002} and {message:id=9360005}

          If you want the number of columns in the result set to depend on the data at run-time, the problem gets more difficult, because the number of columns has to be hard-coded into any query.
          Too bad you're uisng Oracle 10. The Oracle 11 <tt> SELECT ... PIVOT </tt> feature can produce XML output with a variable number of columns.
          String Aggregation is another common work-around. This Oracle-Base page shows several techniques.
          You can use Dynamic SQL to code a variable number of columns.
          {message:id=3527823} has more details on some of these ideas, and other approaches, too.
          • 2. Re: how to pivot a query
            Solomon Yakobson
            Assuming you know upfront what is max number of conferences cod can have (I'll assume 5):
            select  cod,
                    max(case rn when 1 then conference end) conference1,
                    max(case rn when 1 then username end) username1,
                    max(case rn when 1 then qtd end) qtd1,
                    max(case rn when 2 then conference end) conference2,
                    max(case rn when 2 then username end) username2,
                    max(case rn when 2 then qtd end) qtd2,
                    max(case rn when 3 then conference end) conference3,
                    max(case rn when 3 then username end) username3,
                    max(case rn when 3 then qtd end) qtd3,
                    max(case rn when 4 then conference end) conference4,
                    max(case rn when 4 then username end) username4,
                    max(case rn when 4 then qtd end) qtd4,
                    max(case rn when 5 then conference end) conference5,
                    max(case rn when 5 then username end) username5,
                    max(case rn when 5 then qtd end) qtd5
              from  t
              group by cod
              order by cod
            /
            
            COD CONFERENCE1 USE QTD1 CONFERENCE2 USE QTD2 CONFERENCE3 USE QTD3 CONFERENCE4 USE QTD4 CONFERENCE5 USE QTD5
            --- ----------- --- ---- ----------- --- ---- ----------- --- ---- ----------- --- ---- ----------- --- ----
             10           1 tom    8           2 kim    7
             20           1 jos   10           2 joe   11           3 bil   12
            
            SQL> 
            SY.