7 Replies Latest reply: Jul 12, 2010 6:21 AM by 748507 RSS

    columns reorder, rename and pivot

    748507
      Please help:
      I have table ‘orders’
      client, person, person_code, type, time_sec, month
      MILE, John, John1, G, 12, May-2010
      MILE, John, John1, G, 2, May-2010
      MILE, John, John1, G, 10, May-2010
      MILE, John, John1, P, 12, May-2010
      MILE, John, John1, P, 2, May-2010
      MILE, John, John1, P, 10, May-2010
      … and so on

      Query
      SELECT client, person, person_code, type, sum(time_sec) time_sec
      FROM orders
      WHERE month = ‘May-2010’
      GROUP BY client, person, person_code, time_sec, type
      ORDER BY client, type asc;

      I get:
      Client, person, person_code, type, time_sec
      MILE, John, John1, G, 24
      MILE, John, John1, P, 24

      How to get to get?
      -     different column order, names and pivot for type

      client, person, person_code, total_time_sec, sum_type_G, sum_type_P
      MILE, John, John1, 48, 24, 24

      Please help.
        • 1. Re: columns reorder, rename and pivot
          Frank Kulash
          Hi,

          The following thread shows that basics of pivoting:
          Help for a query to add columns

          That example happens to use the aggregate COUNT function. For what you want, use the aggregate MIN (or MAX) and SUM functions the same way.

          Edited by: Frank Kulash on Jul 12, 2010 6:41 AM
          • 2. Re: columns reorder, rename and pivot
            Centinul
            Give this a shot:
            SELECT client
                 , person
                 , person_code
                 , MAX(DECODE(typ,'ALL',time_sec)) AS total_time_sec
                 , MAX(DECODE(typ,'G',time_sec))   AS sum_type_G
                 , MAX(DECODE(typ,'P',time_sec))   AS sum_type_p
            FROM
            (
                    SELECT client
                         , person
                         , person_code
                         , DECODE
                           ( GROUPING(type)
                           , 1,'ALL'
                           , type
                           )                 AS typ
                         , SUM(time_sec)     AS time_sec
                    FROM   orders
                    GROUP BY client
                           , person
                           , person_code
                           , ROLLUP(type)
            )
            GROUP BY client
                   , person
                   , person_code
            This is the 10g and prior way to pivot data. In 11g there is a PIVOT clause that you can use. Unfortunately I don't have 11g so I couldn't provide that query.
            • 3. Re: columns reorder, rename and pivot
              Solomon Yakobson
              with orders as (
                              select 'MILE' client,'John' person,'John1' person_code,'G' type_val, 12 time_sec,date '2010-05-01' month from dual union all
                              select 'MILE','John','John1','G', 2,date '2010-05-01' from dual union all
                              select 'MILE','John','John1','G', 10,date '2010-05-01' from dual union all
                              select 'MILE','John','John1','P', 12,date '2010-05-01' from dual union all
                              select 'MILE','John','John1','P', 2,date '2010-05-01' from dual union all
                              select 'MILE','John','John1','P', 10,date '2010-05-01' from dual
                             )
              select  client,
                      person,
                      person_code,
                      sum(time_sec) total_time_sec,
                      sum(case type_val when 'G' then time_sec end) sum_type_G,
                      sum(case type_val when 'P' then time_sec end) sum_type_P
                from  orders
                group by client,
                         person,
                         person_code
                order by client,
                         person,
                         person_code
              /
              
              CLIE PERS PERSO TOTAL_TIME_SEC SUM_TYPE_G SUM_TYPE_P
              ---- ---- ----- -------------- ---------- ----------
              MILE John John1             48         24         24
              
              SQL> 
              SY.
              • 4. Re: columns reorder, rename and pivot
                Centinul
                Much better solution Solomon. I guess I have a case of the "Mondays." :)
                • 5. Re: columns reorder, rename and pivot
                  748507
                  Thank you, but I can not change the original data as I have about 40000 rows, is there a way to this without 'with order as'?

                  Thank you.
                  • 6. Re: columns reorder, rename and pivot
                    Centinul
                    Izzy wrote:
                    Thank you, but I can not change the original data as I have about 40000 rows, is there a way to this without 'with order as'?
                    The entire 'WITH' clause generates a fake table. Since you did not provide CREATE / INSERT statements forum members often do this to generate sample data. You can remove the entire WITH clause and it should work.
                    • 7. Re: columns reorder, rename and pivot
                      748507
                      Thanks :) I'll try this