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à!
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
Thanks Charles – a good example that shows there are many ways to skin a cat!
The forums have a lot to answer for! *{;-)
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! *{;-)
[…] a corollary to the previous entry in this series, we can take the question […]