2 Replies Latest reply: Dec 11, 2012 9:09 AM by Peter vd Zwan RSS

    Dynamic pivot

    user9093300
      Hi All,
      Below is the code for my Table.

      CREATE TABLE SCOPE(

      TYPE varchar2(100),
      SPECIALIST varchar2(100),
      UPDATE_COUNT Number

      );

      INSERT INTO SCOPE VALUES('Updates','Hari',1);
      INSERT INTO SCOPE VALUES('Updates','Hari',17);
      INSERT INTO SCOPE VALUES('Updates','Rakesh',12);
      INSERT INTO SCOPE VALUES('Updates','Rakesh',6);
      INSERT INTO SCOPE VALUES('Legis','Rakesh',6);
      INSERT INTO SCOPE VALUES('Legis','Rakesh',10);
      INSERT INTO SCOPE VALUES('Legis','Hari',10);
      INSERT INTO SCOPE VALUES('Legis','Hari',10);
      INSERT INTO SCOPE VALUES('Legis','Hari',4);
      INSERT INTO SCOPE VALUES('Ins','Rakesh',4);
      INSERT INTO SCOPE VALUES('Ins','Rakesh',14);
      INSERT INTO SCOPE VALUES('Ins','Rakesh',6);
      INSERT INTO SCOPE VALUES('Multibases','Rakesh',6);
      INSERT INTO SCOPE VALUES('Multibases','Rakesh',6);
      INSERT INTO SCOPE VALUES('Multibases','Rakesh',6);



      here is my query

      SELECT type, Hari, Rakesh FROM (

      SELECT TYPE, Specialist, UPDATE_COUNT
      FROM Scope
      )
      PIVOT
      (
      SUM(UPDATE_COUNT) FOR Specialist IN ('Rakesh' as Rakesh, 'Hari' as hari)

      )
      ORDER BY Type


      i need a query that will make a new row and column as total. column_tot should show the sum of the columns and row_tot should show sum of the rows.

      Thanks.
        • 1. Re: Dynamic pivot
          6363
          Please actually read the section on dynamic pivoting as instructed in your other thread for this question.

          {message:id=10723603}
          jeneesh wrote:
          Read FAQ: {message:id=9360005}

          Check dynamic pivoting..
          And if you have any specific questions about the instructions, post them there and close and mark this duplicate question as answered.
          • 2. Re: Dynamic pivot
            Peter vd Zwan
            Hi,

            Try rhis:
            with scope_2 as
            (
            select
              case  when grouping(type) = 0 then min(type)
                    else 'total' end                                type
              ,case when grouping(specialist) = 0 then min(specialist)
                    else 'Total' end                                specialist
              ,sum(update_count)                                    update_count
            from
              scope
            group by
            cube (type, specialist)
            )
              SELECT
                *
              FROM
                Scope_2
              
              PIVOT
              (
              SUM(UPDATE_COUNT) FOR Specialist IN ('Rakesh' as Rakesh, 'Hari' as hari, 'Total' as col_total)
              )
            
            ORDER BY
              Type
            ;
            TYPE                                                                                                 RAKESH HARI COL_TOTAL
            ---------------------------------------------------------------------------------------------------- ------ ---- ---------
            Ins                                                                                                      24             24 
            Legis                                                                                                    16   24        40 
            Multibases                                                                                               18             18 
            Updates                                                                                                  18   18        36 
            total                                                                                                    76   42       118 
            Regards,

            Peter