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: 64622Registered: 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
 Previous Topic: Linux to windows Next Topic: JOBS in Oracle using DBMS_JOB
Goto Forum:

Current Time: Wed Mar 29 15:57:39 CDT 2017

Total time taken to generate the page: 0.13006 seconds