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) ** Thu, 17 January 2008 07:06
 rajavu1 Messages: 1574Registered: 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: 68597Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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    )
21  select 1 nop,
23  from cols
24  where col = &lv
25  connect by prior col = 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
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
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 #294938 is a reply to message #294381] Sun, 20 January 2008 23:24
 rajavu1 Messages: 1574Registered: May 2005 Location: Bangalore , India Senior Member
Hi Michel ,

It is simply Awesome .... You are rocking .

Rajuvan
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: 1574Registered: 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 #295829 is a reply to message #294381] Wed, 23 January 2008 12:30
 varu123 Messages: 754Registered: October 2007 Senior Member
What is this language called?
I thought SQl is simple
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: 105Registered: 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
;```
 Previous Topic: Puzzle n°04 - Evenly share batches of articles into groups *** Next Topic: MOS severity levels
Goto Forum:

Current Time: Sat Feb 24 07:33:33 CST 2024