Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    Lightbulb Unanswered: data in matrix format..

    hello oracle developers..
    can u help me in writing the sql query to get the "count of each job value" per deptno...like in the following format..

    deptno manager salesman clerk
    10 2 3 2
    20 2 2 1
    30 1 3 2

    please help me out..
    thanks in advance..
    Last edited by sridharreddy_d; 01-23-04 at 07:43.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: data in matrix format..

    Code:
    SQL> select deptno
      2  ,      count(DECODE(job,'ANALYST', 1)) as "ANALYST"
      3  ,      count(DECODE(job,'CLERK', 1)) as "CLERK"
      4  ,      count(DECODE(job,'MANAGER', 1)) as "MANAGER"
      5  ,      count(DECODE(job,'PRESIDENT', 1)) as "PRESIDENT"
      6  ,      count(DECODE(job,'SALESMAN', 1)) as "SALESMAN"
      7  from   emp
      8  group by deptno
      9  order by deptno
     10  /
    
        DEPTNO    ANALYST      CLERK    MANAGER  PRESIDENT   SALESMAN
    ---------- ---------- ---------- ---------- ---------- ----------
            10          0          1          1          1          0
            20          2          2          1          0          0
            30          0          1          1          0          4
    (I have a utility I wrote that enables me to knock these "pivot queries" up fast!)

  3. #3
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    Re: data in matrix format..

    Originally posted by andrewst
    Code:
    SQL> select deptno
      2  ,      count(DECODE(job,'ANALYST', 1)) as "ANALYST"
      3  ,      count(DECODE(job,'CLERK', 1)) as "CLERK"
      4  ,      count(DECODE(job,'MANAGER', 1)) as "MANAGER"
      5  ,      count(DECODE(job,'PRESIDENT', 1)) as "PRESIDENT"
      6  ,      count(DECODE(job,'SALESMAN', 1)) as "SALESMAN"
      7  from   emp
      8  group by deptno
      9  order by deptno
     10  /
    
        DEPTNO    ANALYST      CLERK    MANAGER  PRESIDENT   SALESMAN
    ---------- ---------- ---------- ---------- ---------- ----------
            10          0          1          1          1          0
            20          2          2          1          0          0
            30          0          1          1          0          4
    (I have a utility I wrote that enables me to knock these "pivot queries" up fast!)

    thanks a lot..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •