This discussion is archived
2 Replies Latest reply: Dec 11, 2012 7:09 AM by Peter vd Zwan RSS

Dynamic pivot

user9093300 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

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