Home » Other » General » Puzzle n°08 - Display the Numerical Pyramid using straight SQL query (Not PL/SQL) **
| Puzzle n°08 - Display the Numerical Pyramid using straight SQL query (Not PL/SQL) ** [message #294381] |
Thu, 17 January 2008 07:06  |
 |
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
|
Senior Member |
|
|
Hi All,
The aim of this Puzzle is to display a pyramid of Numbers using SQL query . It might be easy with PL/SQL routines. So it should be using the straight SQL. The output will be as follows
SQL> DEFINE LV=10
SQL> /
A B C D E F G H I J
---- ---- ---- ---- ---- ---- ---- ---- ---- ----
1 2 3 4 5 6 7 8 9 10
11 12 13 14 15 16 17 18
19 20 21 22 23 24
25 26 27 28
29 30
SQL> DEFINE LV=7
SQL> /
A B C D E F G H I J
---- ---- ---- ---- ---- ---- ---- ---- ---- ----
1 2 3 4 5 6 7
8 9 10 11 12
13 14 15
16
SQL> DEFINE LV=5
SQL> /
A B C D E F G H I J
---- ---- ---- ---- ---- ---- ---- ---- ---- ----
1 2 3 4 5
6 7 8
9
Rajuvan.
[Updated on: Sun, 15 June 2014 00:20] by Moderator Report message to a moderator |
|
|
|
| Re: Puzzle n°08 - Display the Numerical Pyramid using straight SQL query (Not PL/SQL) ** [message #294615 is a reply to message #294381] |
Fri, 18 January 2008 03:38  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
It is more an arithmetic problem than a SQL one.
The question is how to put number in (line,col) coordinates:
SQL> set numwidth 3
SQL> def lv=10
SQL> with
2 lines as (
3 select level line, -- line number
4 &lv-level+1 last_col, -- last column with a value
5 (level-1)*(&lv-level+2) nb_prev -- number of numbers in previous lines
6 from dual
7 connect by level <= trunc((&lv+1)/2)
8 )
9 select -- col 1
10 case when 1 < line then null
11 when 1 > last_col then null
12 else nb_prev+1-(line-1)
13 end a,
14 -- col 2
15 case when 2 < line then null
16 when 2 > last_col then null
17 else nb_prev+2-(line-1)
18 end b,
19 -- col 3
20 case when 3 < line then null
21 when 3 > last_col then null
22 else nb_prev+3-(line-1)
23 end c,
24 -- col 4
25 case when 4 < line then null
26 when 4 > last_col then null
27 else nb_prev+4-(line-1)
28 end d,
29 -- col 5
30 case when 5 < line then null
31 when 5 > last_col then null
32 else nb_prev+5-(line-1)
33 end e,
34 -- col 6
35 case when 6 < line then null
36 when 6 > last_col then null
37 else nb_prev+6-(line-1)
38 end f,
39 -- col 7
40 case when 7 < line then null
41 when 7 > last_col then null
42 else nb_prev+7-(line-1)
43 end g,
44 -- col 8
45 case when 8 < line then null
46 when 8 > last_col then null
47 else nb_prev+8-(line-1)
48 end h,
49 -- col 9
50 case when 9 < line then null
51 when 9 > last_col then null
52 else nb_prev+9-(line-1)
53 end i,
54 -- col 10
55 case when 10 < line then null
56 when 10 > last_col then null
57 else nb_prev+10-(line-1)
58 end j
59 from lines
60 order by line
61 /
A B C D E F G H I J
--- --- --- --- --- --- --- --- --- ---
1 2 3 4 5 6 7 8 9 10
11 12 13 14 15 16 17 18
19 20 21 22 23 24
25 26 27 28
29 30
5 rows selected.
SQL> def lv=7
SQL> /
A B C D E F G H I J
--- --- --- --- --- --- --- --- --- ---
1 2 3 4 5 6 7
8 9 10 11 12
13 14 15
16
4 rows selected.
SQL> def lv=5
SQL> /
A B C D E F G H I J
--- --- --- --- --- --- --- --- --- ---
1 2 3 4 5
6 7 8
9
3 rows selected.
To add a little bit more of SQL chalenge and trying to get the exact number of columns displayed.
Build a query that give the correct number for each line and column:
SQL> with
2 lines as ( -- row generator for each output line
3 select level line, -- line number
4 &lv-level+1 last_col, -- last column with a value
5 (level-1)*(&lv-level+2) nb_prev -- number of numbers in previous lines
6 from dual
7 connect by level <= trunc((&lv+1)/2)
8 ),
9 cols as ( -- row generator for each output column
10 select level col from dual connect by level <= &lv
11 )
12 select line, col,
13 case when col < line then ' '
14 when col > last_col then ' '
15 else to_char(nb_prev+col-(line-1),'99')
16 end data
17 from lines, cols
18 order by line, col
19 /
LINE COL DAT
---- --- ---
1 1 1
1 2 2
1 3 3
1 4 4
1 5 5
2 1
2 2 6
2 3 7
2 4 8
2 5
3 1
3 2
3 3 9
3 4
3 5
15 rows selected.
Now use any pivot method to put them line by line, for instance (adding the header):
SQL> set head off
SQL> col nop noprint
SQL> col line format a50
SQL> def lv=10
SQL> with
2 lines as ( -- row generator for each output line
3 select level line, -- line number
4 &lv-level+1 last_col, -- last column with a value
5 (level-1)*(&lv-level+2) nb_prev -- number of numbers in previous lines
6 from dual
7 connect by level <= trunc((&lv+1)/2)
8 ),
9 cols as ( -- row generator for each output column
10 select level col from dual connect by level <= &lv
11 ),
12 results as ( -- result values distributed into lines and columns
13 select line, col,
14 case when col < line then ' '
15 when col > last_col then ' '
16 else to_char(nb_prev+col-(line-1),'99')
17 end data
18 from lines, cols
19 )
20 -- Display header
21 select 1 nop,
22 replace(substr(sys_connect_by_path(lpad(chr(ascii('A')+col-1),3),'/'),2),'/',' ')
23 from cols
24 where col = &lv
25 connect by prior col = col-1
26 start with col = 1
27 union all
28 -- Display "-" line
29 select 2, replace(substr(sys_connect_by_path('---','/'),2),'/',' ')
30 from cols
31 where col = &lv
32 connect by prior col = col-1
33 start with col = 1
34 union all
35 -- Display result lines
36 select 2+line,
37 replace(substr(sys_connect_by_path(data,'/'),2),'/',' ') line
38 from results
39 where col = &lv
40 connect by prior line = line and prior col = col-1
41 start with col = 1
42 order by 1
43 /
A B C D E F G H I J
--- --- --- --- --- --- --- --- --- ---
1 2 3 4 5 6 7 8 9 10
11 12 13 14 15 16 17 18
19 20 21 22 23 24
25 26 27 28
29 30
7 rows selected.
SQL> def lv=7
SQL> /
A B C D E F G
--- --- --- --- --- --- ---
1 2 3 4 5 6 7
8 9 10 11 12
13 14 15
16
6 rows selected.
SQL> def lv=5
SQL> /
A B C D E
--- --- --- --- ---
1 2 3 4 5
6 7 8
9
5 rows selected.
SQL> def lv=12
SQL> /
A B C D E F G H I J K L
--- --- --- --- --- --- --- --- --- --- --- ---
1 2 3 4 5 6 7 8 9 10 11 12
13 14 15 16 17 18 19 20 21 22
23 24 25 26 27 28 29 30
31 32 33 34 35 36
37 38 39 40
41 42
8 rows selected.
Then we are no more limited in the number of columns.
Regards
Michel
|
|
|
|
|
|
| Re: Puzzle n°08 - Display the Numerical Pyramid using straight SQL query (Not PL/SQL) ** [message #294944 is a reply to message #294381] |
Sun, 20 January 2008 23:44 |
|
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
|
Senior Member |
|
|
I have just tried code for the pyramid before i posted the actual Post . Let me share my code which is slightly complex than Michel's one .
SQL> WITH ASET AS
2 (
3 SELECT du.*,
4 LEVEL L,
5 NVL(LAG((x-1) - (level -1)*2) OVER (PARTITION BY null ORDER BY LeVEL),0) n
6 FROM (
7 SELECT max(DECODE(LEVEL,1,1)) A,
8 max(DECODE(LEVEL,2,2)) B,
9 max(DECODE(LEVEL,3,3)) C,
10 max(DECODE(LEVEL,4,4)) D,
11 max(DECODE(LEVEL,5,5)) E,
12 max(DECODE(LEVEL,6,6)) F,
13 max(DECODE(LEVEL,7,7)) G,
14 max(DECODE(LEVEL,8,8)) H,
15 max(DECODE(LEVEL,9,9)) I,
16 max(DECODE(LEVEL,10,10)) J,
17 max(LV) x
18 FROM( SELECT &LV LV
19 FROM DUAL )
20 CONNECt by LEVEL <= LV) DU
21 CONNECT by LEVEL < (X/2)+1 ),
22 bset as ( select a,b,c,d,e,f,g,h,i,j,x,L ,n , sum(n) over (partition by null order by l) p
23 from aset )
24 select DECODE(SIGN(a- (L -1) ),1 , DECODE(SIGN((a+l-1)-x), 0, a+P ,-1 ,a+P) ) A ,
25 DECODE(SIGN(b- (L -1) ),1 ,DECODE(SIGN((b+l-1)-x), 0, b+P,-1, b+P) ) B ,
26 DECODE(SIGN(c- (L -1) ),1 , DECODE(SIGN((c+l-1)-x), 0,c+P,-1,c+P) ) C ,
27 DECODE(SIGN(d- (L -1) ),1 , DECODE(SIGN((d+l-1)-x), 0,d+P,-1,d+P) ) D ,
28 DECODE(SIGN(e- (L -1) ),1 , DECODE(SIGN((e+l-1)-x), 0,e+P,-1,e+P) ) E ,
29 DECODE(SIGN(f- (L -1) ),1 , DECODE(SIGN((f+l-1)-x), 0,f+P,-1,F+P) ) F ,
30 DECODE(SIGN(g- (L -1) ),1 , DECODE(SIGN((g+l-1)-x), 0,g+P,-1,g+P) ) G ,
31 DECODE(SIGN(h- (L -1) ),1 , DECODE(SIGN((h+l-1)-x), 0,h+P,-1,h+P) ) H ,
32 DECODE(SIGN(i- (L -1) ),1 , DECODE(SIGN((i+l-1)-x), 0,i+P,-1,i+P) ) I ,
33 DECODE(SIGN(j- (L -1) ),1 , DECODE(SIGN((j+l-1)-x), 0,j+P,-1,j+P) ) J
34 from Bset;
A B C D E F G H I J
--- --- --- --- --- --- --- --- --- ---
1 2 3 4 5 6 7 8 9 10
11 12 13 14 15 16 17 18
19 20 21 22 23 24
25 26 27 28
29 30
SQL>
Michel's solution is more elegant
Still expecting much simpler method from experts 
Rajuvan
[Updated on: Sun, 20 January 2008 23:45] Report message to a moderator |
|
|
|
|
|
| Re: Puzzle n°08 - Display the Numerical Pyramid using straight SQL query (Not PL/SQL) ** [message #687421 is a reply to message #295829] |
Tue, 07 March 2023 13:37 |
 |
mathguy
Messages: 108
Registered: January 2023
|
Senior Member |
|
|
I will take the view that the result must be in ten columns (rather than just a single trompe l'oeil column that, when rendered graphically, looks like multiple columns). That is, view it as a "data processing" problem rather than a "displaying" or "reporting" problem.
In that case the number of columns must be given in advance, it can't depend on the input LVL (unless we use dynamic SQL, which - let's leave that alone).
The computation can be all done in closed form, which is tedious but perhaps seventh- or eighth- grade level. (Or college level in some countries, depending on the education system.) But since this is a forum for SQL, not for arithmetic, perhaps it is better to let SQL do most of the work.
In the solution below I generate column numbers from 1 to 10 (hard-coded), then row numbers from 1 to CEIL(LVL/2) (this will depend on LVL). This models the two-dimensional array required in the output, but modeled by row number and column number; in the last step, we will pivot to get the desired format. Then the problem is simply to determine which "cells" will be non-NULL, which is a trivial task, and then assign consecutive integers to the cells - that is trivial with the ROW_NUMBER() analytic function.
In the solution below I model the input LVL as a bind variable, I use the WITH clause with column aliases in each declaration (available only since Oracle 11.2, but this is not essential - everything can be written with old-style subqueries), and I use the PIVOT operator available only since Oracle 11.1, but that too can be done the old way, with conditional aggregation. Even the analytic function is not critical - we can get the same result with an ORDER BY clause and an outer query where we select ROWNUM. The solution can be written in very, very old versions of Oracle.
As an aside, why is the result called a pyramid? A pyramid is a three-dimensional thing; what we have here is a triangle. (By analogy, in combinatorics we have Pascal's triangle, nobody calls it Pascal's "pyramid".)
with
c (cn) as (select level from dual connect by level <= 10),
r (rn) as (select cn from c where cn <= ceil(:lvl/2)),
prep (rn, cn, val) as (
select rn, cn, row_number() over (order by rn, cn)
from c join r on cn between rn and :lvl + 1- rn
)
select a, b, c, d, e, f, g, h, i, j
from prep
pivot (min(val) for cn in (1 a, 2 b, 3 c, 4 d, 5 e, 6 f, 7 g, 8 h, 9 i, 10 j))
order by rn
; |
|
|
|
Goto Forum:
Current Time: Fri Nov 22 11:05:12 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
