HomeUncategorized › Writing “complex” SQL – a how to, pt1

Writing “complex” SQL – a how to, pt1

Not so long ago, someone on our work Oracle IM channel asked a question, and then several people were amazed at the solution provided. I’m not sure why; it doesn’t seem so difficult to me, but it strikes me that the practice I’ve had via the SQL & PL/SQL OTN Forum probably has a lot to do with that.

So, anyway, I thought I would start a little series on writing complicated sql, by taking you through the steps I would typically take in order to solve the requirement.

I’ll start with that simple request from my colleague:

I need to display the numbers 1-12 in 3 rows of four columns, like so:

1 2 3 4
5 6 7 8
9 10 11 12

Looking in my SQL toolbox, the very first thing I reach for is how to create the list of numbers from 1 to 12:

with nums as (select level rn
              from   dual
              connect by level <= 12)
select *
from   nums;

which gives us:

   RN
-----
    1
    2
    3
    4
    5
    6
    7
    8
    9
   10
   11
   12

Next thing, I know I need to pivot the results (I should add that I don't have 11g yet, so can't use the nice new functionality!), so now I know I need two things:

  • how to identify the row a number belongs to
  • how to identify the column a number belongs to

Let's take the first item - identifying the row each number belongs to. We know that the numbers 1-4 will go on the first row, 5-8 on the second and 9-12 on the third. I can acheive that by dividing each number by 4 and then take the ceiling value of it, like so:

with nums as (select level rn, ceil(level/4) grp
              from   dual
              connect by level <= 12)
select *
from   nums;

which gives us:

   RN        GRP
----- ----------
    1          1
    2          1
    3          1
    4          1
    5          2
    6          2
    7          2
    8          2
    9          3
   10          3
   11          3
   12          3

Next, I need to work out the column position each number belongs in - that's where the mod() function comes in handy!

with nums as (select level rn, ceil(level/4) grp, mod(level, 4) col_pos
              from   dual
              connect by level <= 12)
select *
from   nums;

which gives us:

   RN        GRP    COL_POS
----- ---------- ----------
    1          1          1
    2          1          2
    3          1          3
    4          1          0
    5          2          1
    6          2          2
    7          2          3
    8          2          0
    9          3          1
   10          3          2
   11          3          3
   12          3          0

So, having identified the row and the column position, we can now use pivoting to display the numbers in the desired format:

with nums as (select level rn, ceil(level/4) grp, mod(level, 4) col_pos
              from   dual
              connect by level <= 12)
select max(case when col_pos = 1 then rn end) col1,
       max(case when col_pos = 2 then rn end) col2,
       max(case when col_pos = 3 then rn end) col3,
       max(case when col_pos = 0 then rn end) col4
from   nums
group by grp;

Giving us:

 COL1       COL2       COL3       COL4
----- ---------- ---------- ----------
    1          2          3          4
    5          6          7          8
    9         10         11         12

Et voilà!

5 Comments.[ Leave a comment ]

  1. Helping out on the OTN forums certainly offers a lot of chances for practice.

    If the problem was posed to me, I probably would have developed a slight variation of what you produced:

    SELECT
    ROWNUM RN
    FROM
    DUAL
    CONNECT BY
    LEVEL<=12

    RN
    ———-
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12

    SELECT
    RN,
    TRUNC((RN-1)/4)+1 ROW_VAL,
    MOD(RN-1,4)+1 COLUMN_VAL
    FROM
    (SELECT
    ROWNUM RN
    FROM
    DUAL
    CONNECT BY
    LEVEL<=12);

    RN ROW_VAL COLUMN_VAL
    ———- ———- ———-
    1 1 1
    2 1 2
    3 1 3
    4 1 4
    5 2 1
    6 2 2
    7 2 3
    8 2 4
    9 3 1
    10 3 2
    11 3 3
    12 3 4

    SELECT
    MAX(DECODE(COLUMN_VAL,1,RN,NULL)) C1,
    MAX(DECODE(COLUMN_VAL,2,RN,NULL)) C2,
    MAX(DECODE(COLUMN_VAL,3,RN,NULL)) C3,
    MAX(DECODE(COLUMN_VAL,4,RN,NULL)) C4
    FROM
    (SELECT
    RN,
    TRUNC((RN-1)/4)+1 ROW_VAL,
    MOD(RN-1,4)+1 COLUMN_VAL
    FROM
    (SELECT
    ROWNUM RN
    FROM
    DUAL
    CONNECT BY
    LEVEL<=12))
    GROUP BY
    ROW_VAL;

    C1 C2 C3 C4
    ———- ———- ———- ———-
    1 2 3 4
    5 6 7 8
    9 10 11 12

  2. Thanks Charles – a good example that shows there are many ways to skin a cat!

    The forums have a lot to answer for! *{;-)

  3. The art of breaking complex SQL down into a series of incremental steps is knowing what to use as the starting point. In the example you give, even the basic query relies on knowing a slightly arcane trick. I say “trick” advisedly, as that usage of CONNECT BY with LEVEL is not explicitly supported by the documentation (although once you know about it you can see it’s not actually illegal). Also it didn’t work in quite the same way in certain point releases of 9i, which suggests it is a side-effect rather than Status By Design.

    • Aye, I agree that it’s a (useful) trick, but regarding the issues in 9i, I think they were more issues in SQL*Plus than the database. (I assume you’re referring to the way the results didn’t always display correctly if you didn’t make it a subquery?).

      As for it being slightly arcane, I thought I’d already covered it in one of my tips&tricks posts, but looks like I didn’t get round to covering it! Oh well… there’s the next post’s subject! *{;-)

  4. [...] a corollary to the previous entry in this series, we can take the question [...]

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>