Forum Stats

  • 3,851,702 Users
  • 2,264,013 Discussions
  • 7,904,820 Comments

Discussions

Need some help with PIVOT

2788234
2788234 Member Posts: 4
edited Nov 5, 2014 11:41AM in SQL & PL/SQL

Hello,

First, let me apologize if this is in the wrong forum.

I need some help with the pivot command in Oracle.  Here is what I am trying to accomplish:

select persongroup,status, count(status) as tot

from workorder

where status in ('WSCH','WPLAN','WMATL','PLAN','APPR')

and persongroup in ('K3P-GEN','K3P-ELEC','K3P-INST','K3P-MECH')

group by  persongroup,status

order by 1


Results:


K3P-ELEC APPR 12

K3P-ELEC PLAN 10

K3P-ELEC WMATL 10

K3P-ELEC WPLAN 109

K3P-ELEC WSCH 18

K3P-GEN APPR 17

K3P-GEN PLAN 59

K3P-GEN WMATL 10

K3P-GEN WPLAN 188

K3P-GEN WSCH 22

K3P-INST APPR 18

K3P-INST PLAN 27

K3P-INST WMATL 17

K3P-INST WPLAN 60

K3P-INST WSCH 40

K3P-MECH APPR 4

K3P-MECH PLAN 47

K3P-MECH WMATL 14

K3P-MECH WPLAN 20

K3P-MECH WSCH 52

What I want to see is :

                        APPR     PLAN     WMATL     WPLAN     WSCH     TOTAL

K3P-ELEC         12          10               10               109          18          159

K3P-GEN          17          59               10               188           22          296

......

Using the query above I tried this but it is incorrect;

select * from (

select persongroup,status, count(status) as tot

from   workorder

where status in ('WSCH','WPLAN','WMATL','PLAN','APPR')

and persongroup in ('K3P-GEN','K3P-ELEC','K3P-INST','K3P-MECH')

group by  persongroup,status

order by 1

  sum(3)

  for status in ('WSCH','WPLAN','WMATL','PLAN','APPR')

order by 1



Any help appreciated.


Thank you


Cameron

Tagged:

Best Answer

  • L-MachineGun
    L-MachineGun Member Posts: 926 Silver Badge
    Answer ✓

    You missed the "PIVOT" option, try this:

    SELECT *

      FROM (  SELECT Persongroup, Status, COUNT ( Status ) AS Tot

                FROM Workorder

              WHERE Status IN ('WSCH', 'WPLAN', 'WMATL', 'PLAN', 'APPR')

                AND Persongroup IN ('K3P-GEN', 'K3P-ELEC', 'K3P-INST', 'K3P-MECH')

              GROUP BY Persongroup, Status)

    PIVOT (SUM ( Tot ) FOR Status IN  ('WSCH', 'WPLAN', 'WMATL', 'PLAN', 'APPR'))

    ORDER BY 1

    /


Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,477 Red Diamond
    edited Nov 4, 2014 4:27PM

    Hi, Cameron,

    2788234 wrote:
    
    Hello,
    
    First, let me apologize if this is in the wrong forum.
    
    I need some help with the pivot command in Oracle.  Here is what I am trying to accomplish:
    
    select persongroup,status, count(status) as tot
    
    from workorder 
    
    where status in ('WSCH','WPLAN','WMATL','PLAN','APPR')
    
    and persongroup in ('K3P-GEN','K3P-ELEC','K3P-INST','K3P-MECH')
    
    group by  persongroup,status
    
    order by 1
    
    Results:
    K3P-ELEC APPR 12 K3P-ELEC PLAN 10 K3P-ELEC WMATL 10 K3P-ELEC WPLAN 109 K3P-ELEC WSCH 18 K3P-GEN APPR 17 K3P-GEN PLAN 59 K3P-GEN WMATL 10 K3P-GEN WPLAN 188 K3P-GEN WSCH 22 K3P-INST APPR 18 K3P-INST PLAN 27 K3P-INST WMATL 17 K3P-INST WPLAN 60 K3P-INST WSCH 40 K3P-MECH APPR 4 K3P-MECH PLAN 47 K3P-MECH WMATL 14 K3P-MECH WPLAN 20 K3P-MECH WSCH 52 What I want to see is :                         APPR     PLAN     WMATL     WPLAN     WSCH     TOTAL K3P-ELEC         12          10               10               109          18          159 K3P-GEN          17          59               10               188           22          296 ...... Using the query above I tried this but it is incorrect; select * from ( select persongroup,status, count(status) as tot from   workorder where status in ('WSCH','WPLAN','WMATL','PLAN','APPR') and persongroup in ('K3P-GEN','K3P-ELEC','K3P-INST','K3P-MECH') group by  persongroup,status order by 1   sum(3)   for status in ('WSCH','WPLAN','WMATL','PLAN','APPR') order by 1

    Any help appreciated.
    Thank you
    Cameron

    You have a syntax error: unbalanced parentheses.  You have 6 left '('s, but only 5 right ')'s.

    You may have other problems, also.

    Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.

    Also post the exact results you want from that data, and an explanation of how you get those results from that data, with specific examples.

    Always say which version of Oracle you're using (for example, 11.2.0.2.0).

    See the forum FAQ:

  • L-MachineGun
    L-MachineGun Member Posts: 926 Silver Badge
    Answer ✓

    You missed the "PIVOT" option, try this:

    SELECT *

      FROM (  SELECT Persongroup, Status, COUNT ( Status ) AS Tot

                FROM Workorder

              WHERE Status IN ('WSCH', 'WPLAN', 'WMATL', 'PLAN', 'APPR')

                AND Persongroup IN ('K3P-GEN', 'K3P-ELEC', 'K3P-INST', 'K3P-MECH')

              GROUP BY Persongroup, Status)

    PIVOT (SUM ( Tot ) FOR Status IN  ('WSCH', 'WPLAN', 'WMATL', 'PLAN', 'APPR'))

    ORDER BY 1

    /


  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,477 Red Diamond
    edited Nov 4, 2014 4:37PM

    Hi,

    Since I don't have a test version of your table, I'll use scott.emp (which you probably have) to show how to do a pivot.

    If looks like you want to get results like this, for specific jobs (arcross) and deptnos (down):

    DEPTNO 'ANALYST' 'CLERK' 'MANAGER' 'SALESMAN'
    ------ --------- ------- --------- ----------
        10         0       1         1          0
        20         2       2         1          0
        30         0       1         1          4
    
    

    Here's one way to do that in Oracle SQL:

    WITH    relevant_data    AS
    (
        SELECT  deptno, job
        FROM    scott.emp
        WHERE   deptno  IN (10, 20, 30)
        AND     job     IN ('ANALYST', 'CLERK', 'MANAGER', 'SALESMAN')  -- not strictly necessary
    )
    SELECT    *
    FROM      relevant_data
    PIVOT     (    COUNT (*)
              FOR  job  IN ('ANALYST', 'CLERK', 'MANAGER', 'SALESMAN')
              )
    ORDER BY  deptno
    ;
    
    

    You'll notice that the exact same "JOB IN (...)" is used twice.  You'd get the same results without that condition in the sub-query; any job other than the ones listed in the PIVOT clause will be ignored.  It will probably be more efficient to have the condition in the sub-query anyway.

  • 2788234
    2788234 Member Posts: 4

    Thank you.


    My mistake was not missing the PIVOT command - for some reason it did not paste when I copied from TOAD.


    The issue was I had sum(3).  Changed it to sum(tot) and it worked.  I used 3 because sum(count(status)) returned an error.

This discussion has been closed.