This discussion is archived
9 Replies Latest reply: Jul 25, 2010 4:01 PM by 783956 RSS

Is there an easier/simpler way of obtaining this result ?

783956 Journeyer
Currently Being Moderated
Good morning (afternoon to you, BluShadow),

I obtained the following correct, as desired, output (derived from the EMP table):
D10     D20     D30     PREZ    MGRS    ANALS   SALESM  CLERKS
------- ------- ------- ------- ------- ------- ------- -------
CLARK   JONES   WARD    KING    BLAKE   FORD    ALLEN   ADAMS
KING    FORD    TURNER          CLARK   SCOTT   MARTIN  JAMES
MILLER  ADAMS   ALLEN           JONES           TURNER  MILLER
        SMITH   JAMES                           WARD    SMITH
        SCOTT   BLAKE
                MARTIN
using the following query:
 with
   --
   -- pivoted departments  (haven't studied the Oracle PIVOT clause yet)
   --
   depts as
   (
    select max(case deptno
                 when 10 then ename
               end)                                 as d10,
           max(case deptno
                 when 20 then ename
               end)                                 as d20,
           max(case deptno
                 when 30 then ename
               end)                                 as d30,
           rnd
      from (
            select deptno,
                   ename,
                   row_number() over (partition by deptno
                                          order by deptno)  rnd
              from emp
           )
     group by rnd
     order by rnd
   ),
   --
   -- pivoted jobs
   --
   jobs as
   (
    select max(case job
                 when 'CLERK'         then ename
               end)                                 as Clerks,
           max(case job
                 when 'PRESIDENT'     then ename
               end)                                 as Prez,
           max(case job
                 when 'MANAGER'       then ename
               end)                                 as Mgrs,
           max(case job
                 when 'ANALYST'       then ename
               end)                                 as Anals,
           max(case job
                 when 'SALESMAN'      then ename
               end)                                 as SalesM,
           rnj
      from (
            select job,
                   ename,
                   row_number() over (partition by job
                                          order by ename)   as rnj
              from emp
           )
     group by rnj
     order by rnj
   )
select d10,
       d20,
       d30,
       Prez,
       Mgrs,
       Anals,
       SalesM,
       Clerks
  from depts a full outer join jobs b
                            on a.rnd = b.rnj
 order by rnj, rnd;
which takes a total of 5 selects to get there.

I was trying to find a query that would be, hopefully simpler, easier and didn't require as many selects. My last attempt is the following (which is close to the desired result but doesn't get a cigar yet):
select case deptno
         when 10 then ename
       end                                 as d10,
       case deptno
         when 20 then ename
       end                                 as d20,
       case deptno
         when 30 then ename
       end                                 as d30,
       case job
         when 'CLERK'         then ename
       end                                 as Clerks,
       case job
         when 'PRESIDENT'     then ename
       end                                 as Prez,
       case job
         when 'MANAGER'       then ename
       end                                 as Mgrs,
       case job
         when 'ANALYST'       then ename
       end                                 as Anals,
       case job
         when 'SALESMAN'      then ename
       end                                 as SalesM,
       row_number() over (partition by deptno
                              order by deptno)  as rnd,
       row_number() over (partition by job
                              order by ename)   as rnj
  from emp
 order by rnj;
The above query gets me to this result which is encouraging but... short of the mark:
D10     D20     D30     CLERKS  PREZ    MGRS    ANALS   SALESM   RND  RNJ
------- ------- ------- ------- ------- ------- ------- ------- ---- ----
                ALLEN                                   ALLEN      3    1
        ADAMS           ADAMS                                      2    1
                BLAKE                   BLAKE                      6    1
KING                            KING                               2    1
        FORD                                    FORD               1    1
        SCOTT                                   SCOTT              5    2
                JAMES   JAMES                                      5    2
CLARK                                   CLARK                      3    2
                MARTIN                                  MARTIN     2    2
                TURNER                                  TURNER     1    3
MILLER                  MILLER                                     1    3

D10     D20     D30     CLERKS  PREZ    MGRS    ANALS   SALESM   RND  RNJ
------- ------- ------- ------- ------- ------- ------- ------- ---- ----
        JONES                           JONES                      3    3
                WARD                                    WARD       4    4
        SMITH           SMITH                                      4    4
It uses only one SELECT statement and has all the data that needs to be displayed but, I cannot find a way of eliminating the nulls without losing either some jobs or some depts.

Your help is welcome and appreciated,

John.

PS: I'll be perfectly happy learning that there is no easier/simpler way. In other words, if the answer is simply "No, there is no simpler/easier way", please do let me know that is the case. (I ask that you be fairly sure of that though, thank you)

Edited by: 440bx - 11gR2 on Jul 25, 2010 7:19 AM - Added PS.
  • 1. Re: Is there an easier/simpler way of obtaining this result ?
    Hoek Guru
    Currently Being Moderated
    Good afternoon ;) John,

    Unfortunatly I do not have an 11g database at my disposal, but I really think that using the new (UN)PIVOT functionality will make your query (much) simpler and you should really take a look at it.
    Besides spreading links to documentation or Tom Kyte's site, Tim hall also has some great stuff on his site, for example:
    http://www.oracle-base.com/articles/11g/PivotAndUnpivotOperators_11gR1.php
  • 2. Re: Is there an easier/simpler way of obtaining this result ?
    783956 Journeyer
    Currently Being Moderated
    Good afternoon Hoek,

    That PIVOT and UNPIVOT looks like great stuff, particularly when combined with the XML functions as shown in the link you gave me. I've already seen what some XML functions can do, those things are pure magic... can't wait to get to those either.

    I'll be studying PIVOT and UNPIVOT shortly, another chapter and a half to go before I get there (the chapter I'm going through right now is about all kinds of groupings, the next one about hierarchical queries and, then finally, the chapter that includes PIVOTing mixed with another bunch of stuff.)

    I have to admit that I've jumped around in the chapters from a few books, learned a few things on the fly from the replies here in the forum and from pages on the net. Along the way, I ask what if this... (this problem is as a result of one of those "what if this...").

    When I'm done, I'll go thru the entire thing all over again, to make a cohesive whole out of all the stuff I've read. Then, finally, on to PL/SQL.

    Thank you for the good direction :)

    John.
  • 3. Re: Is there an easier/simpler way of obtaining this result ?
    Hoek Guru
    Currently Being Moderated
    Hi John,

    I think many readers on this forum like your enthousiasm, keep it up.
    By the way, I've tried some 'sunday' variations on your query on 10g, ofcourse, but to no avail.
    One thing though, you might want to dive into as well, before or while moving on to PL/SQL is the MODEL clause.
    Very powerful.
    See for example:
    Re: Producing the Mandelbrot set in ascii art?
    Draw SQUARE using SQL
    http://rwijk.blogspot.com/2008/05/string-aggregation-with-model-clause.html
    When I'm done, I'll go thru the entire thing all over again, to make a cohesive whole out of all the stuff I've read.
    You'll never be 'done' ;)

    While you're at it, I'm pretty sure you'll like to read this white-paper, if you didn't already stumble upon it:
    http://www.oracle.com/technology/tech/pl_sql/pdf/doing_sql_from_plsql.pdf
  • 4. Re: Is there an easier/simpler way of obtaining this result ?
    MaximDemenko Pro
    Currently Being Moderated
    As per hoek' suggestion ;-)
    SQL> select d10, d20, d30, prez, mgrs, anals, salesm, clerks
      2    from (select row_number() over(partition by deptno order by ename) rn,
      3                 ename,
      4                 to_char(deptno) deptno
      5            from emp
      6          union all
      7          select row_number() over(partition by job order by ename),
      8                 ename,
      9                 job
     10            from emp) pivot(max(ename) for deptno in(10  d10,
     11                                                     20  d20,
     12                                                     30  d30,
     13                                                     'ANALYST'  anals,
     14                                                     'CLERK'  clerks,
     15                                                     'MANAGER'  mgrs,
     16                                                     'PRESIDENT'  prez,
     17                                                     'SALESMAN'  salesm))
     18   order by rn
     19  /
    
    D10        D20        D30        PREZ       MGRS       ANALS      SALESM     CLERKS
    ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
    CLARK      ADAMS      ALLEN      KING       BLAKE      FORD       ALLEN      ADAMS
    KING       FORD       BLAKE                 CLARK      SCOTT      MARTIN     JAMES
    MILLER     JONES      JAMES                 JONES                 TURNER     MILLER
               SCOTT      MARTIN                                      WARD       SMITH
               SMITH      TURNER
                          WARD
    
    6 rows selected.
    Best regards

    Maxim
  • 5. Re: Is there an easier/simpler way of obtaining this result ?
    Hoek Guru
    Currently Being Moderated
    Thanks, Maxim!
  • 6. Re: Is there an easier/simpler way of obtaining this result ?
    783956 Journeyer
    Currently Being Moderated
    Hi Hoek,

    Thank you :)

    I bumped into the model clause a couple of days ago. Nice way of creating an array of as many dimensions as necessary. Read a little bit about it but haven't played with it yet.

    I'll definitely be checking out the links you've provided (as soon as I find a solution for the problem I'm tackling at this time - the book author "cheated" in his solution, I know I can find one that is true to the problem - maybe another hour or two... or .. oh well)

    Thanks again, the help I've gotten here has been invaluable, I really appreciate the time and effort, it has been extremely helpful.

    Thanks,

    John.
  • 7. Re: Is there an easier/simpler way of obtaining this result ?
    783956 Journeyer
    Currently Being Moderated
    Hi Maxim,

    Thank you! that is beautiful.

    I understood the solution instantly. What your solution accomplishes is to have a column of row numbers that properly groups both the jobs and the depts.

    In what as was trying, as you most likely noticed, the problem I couldn't solve was that, I had 2 sets of mismatching row numbers and I was trying in vain to find a way to make them match.

    Assigning row numbers to each set individually and then joining them is the solution. It is then possible to group by the row numbers because their values coincide in both sets (jobs and depts). Simple, beautiful and completely logical !.

    Great stuff, thank you :)

    John.
  • 8. Re: Is there an easier/simpler way of obtaining this result ?
    Frank Kulash Guru
    Currently Being Moderated
    Hi, John,

    You had part of the solution in each of your attempts.
    Do a FULL OUTER JOIN, as in your first query, on two copies of the result set of your second query.

    Using Oracle 9 features only:
    WITH     got_row_numbers     AS
    (
         select     case WHEN deptno = 10 then ename end               as d10,
                     case WHEN deptno = 20 then ename end                    as d20,
                     case WHEN deptno = 30 then ename end                    as d30,
                     case WHEN job = 'CLERK'     then ename       end        as Clerks,
                     case WHEN job = 'PRESIDENT' then ename       end        as Prez,
                     case WHEN job = 'MANAGER'   then ename       end        as Mgrs,
                     case WHEN job = 'ANALYST'   then ename       end        as Anals,
                     case WHEN job = 'SALESMAN'  then ename       end        as SalesM,
                     row_number () over ( partition by      deptno
                                            order by           NULL
                           )                           as rnd,
                     row_number () over ( partition by      job
                                            order by           ename
                           )                            as rnj
      from      emp
    )
    SELECT       MIN (d.d10)          AS d10
    ,        MIN (d.d20)          AS d20
    ,       MIN (d.d30)          AS d30
    ,       MIN (j.clerks)     AS clerks
    ,       MIN (j.prez)          AS prez
    ,       MIN (j.mgrs)          AS mgrs
    ,       MIN (j.anals)          AS anals
    -- ,        MIN (j.salesm)     AS salesm
    FROM            got_row_numbers     d
    FULL OUTER JOIN     got_row_numbers     j     ON     d.rnd     = j.rnj
    GROUP BY  NVL (d.rnd, j.rnj)
    ORDER BY  NVL (d.rnd, j.rnj)
    ;
    I've been trying to think of a good name for this kind of query where the items one the n-th row have nothing in common except that they are on the n-th row. For lack of anything better, I call it a Prix Fixe Query , because it resembles the menus where, for a fixed price, yuu can choose different options for each course:
    Appetizer     Soup          Main Course     Desert
    ---------     ---------     -------------     ----------
    Pakora          Coconut          Aloo Gobi     Galabjamun
    Salad          Lentil          Bharta          Halwa
    Samosa                    Dhosa          Kulfi
                        Saag Paneer
    Above, each column is sorted alphabeticlly. There is nothing but pure coincidence linking 'Pakora' with 'Coconut' or "Aloo Ghobi' with 'Galabjamun': they just happen the be the first items, in alphabetic order, in their respective columns.

    You may notice that I used
    "PARTITION BY deptno ORDER BY NULL " where you used
    "PARTITION BY deptno ORDER BY deptno "
    It never makes sense to ORDER BY anything in the PARTITION BY list. Each distinct item in the PARTITION BY list is a world of its own. You will only sort things that are identical with respect to the PARTITION BY list. That is, if the system has to decide whether to give 'CLARK' or 'KING' the lower ROW_NUMBER in deptno=10, deptno itself will not help the decision: the deptno for both will necessarily be the same. There's no syntax error, it just doesn't do any good, and the order of the output will be arbitrary.
    There would be a syntax error if you omitted the ORDER BY clause: ROW_NUMBER must be done in the context of some ordering. If you really want the order to be arbitrary, then be clear about it. ORDER BY a constant (such as NULL) so that nobody reading the query quickly is fooled into thinking you really are ordering by something. (A comment would be helpful in that case, also.)
    You probably want to "ORDER BY ename", or something meaningful, in this case.

    Edited by: Frank Kulash on Jul 25, 2010 2:41 PM
    Added digression of "PARTITION BY x ORDER BY x"
  • 9. Re: Is there an easier/simpler way of obtaining this result ?
    783956 Journeyer
    Currently Being Moderated
    Hi Frank :)

    Every time I see a reply from you, I know it's going to be something special and this one is no exception. I was really impressed when I saw only two selects in the solution, that's quite an improvement over the 5 I used.

    I really had to think long and hard to figure out exactly what made your solution work. I finally figured it out.

    The full outer self join is what allows matching the group numbers assigned to depts to the group numbers assigned to jobs. That's what I was trying to do but couldn't figure out how. I thought about doing several types of joins but, my failure was that I never thought about doing the join on
    d.rnd = j.rnj
    (that's the "magic" that makes it all work!)

    Very nice!

    I didn't know I could order by null, when the SQL parser complained that I had not specified an order by, I just slapped the same column as in the partition, knowing full well that it was meaningless. I am in complete agreement that using order by null is much better because it makes it explicit that the clause is there purely to satisfy a syntactical requirement and nothing else. I'll use order by null in those cases from now on.

    I like the Prix Fixe Query, it reminds me of the establishments in Paris that offer them - conjures up nice images :) A derived idea, shorter and similar but, not as nice as far as imagery would be Buffet Query ... Dim Sum Query ? :D

    Thank you again for that very nice query,

    John.

    Edited by: 440bx - 11gR2 on Jul 25, 2010 4:01 PM - Added the word Query to Dim Sum Query

Legend

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