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 Go to next message
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

Thumbs Up
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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 #294938 is a reply to message #294381] Sun, 20 January 2008 23:24 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hi Michel ,


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

Thumbs Up
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 Go to previous messageGo to next message
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 Smile

Thumbs Up
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 Go to previous message
varu123
Messages: 754
Registered: October 2007
Senior Member
What is this language called?
I thought SQl is simple Shocked
Previous Topic: Linux to windows
Next Topic: JOBS in Oracle using DBMS_JOB
Goto Forum:
  


Current Time: Sat Dec 03 12:17:39 CST 2016

Total time taken to generate the page: 0.17958 seconds