## Forum Stats

• 3,817,228 Users
• 2,259,292 Discussions

Discussions

# Pascal's triangle or binomial coefficients

13»

• Member Posts: 5,220 Bronze Trophy
strike !
• Member Posts: 2,208
LOL. Is this better?
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> VARIABLE r NUMBER;
SQL> EXEC :r := 10;

PL/SQL procedure successfully completed.

SQL> SELECT LPAD (' ', 5 * (:r - an) / 2) ||
2           REPLACE (SYS_CONNECT_BY_PATH (
3              LPAD (r, 5), '/'), '/') pascals_triangle
4  FROM  (SELECT a.n an, b.n bn,
5                product (GREATEST (b.n, 1)) OVER (
6                   PARTITION BY a.n) /
7                product (GREATEST (b.n, 1)) OVER (
8                   PARTITION BY a.n ORDER BY b.n) /
9                product (GREATEST (a.n - b.n, 1)) OVER (
10                   PARTITION BY a.n ORDER BY b.n DESC) r
11         FROM   TABLE (many (0, :r)) a,
12                TABLE (many (0, a.n)) b)
13  WHERE  CONNECT_BY_ISLEAF = 1
15  CONNECT BY an = PRIOR an AND bn = PRIOR bn + 1;

PASCALS_TRIANGLE
--------------------------------------------------------------------------------
1
1    1
1    2    1
1    3    3    1
1    4    6    4    1
1    5   10   10    5    1
1    6   15   20   15    6    1
1    7   21   35   35   21    7    1
1    8   28   56   70   56   28    8    1
1    9   36   84  126  126   84   36    9    1
1   10   45  120  210  252  210  120   45   10    1

11 rows selected.

SQL>
• Member Posts: 25,514 Silver Crown
Splendid !!

Nicolas.
• Member Posts: 1,072 Bronze Badge
SQL> col PASCALS_TRIANGLE for a80
SQL>
SQL> with Renban as (
2 select RowNum-1 as Val
3 from dual connect by Level <= 15),
4 WorkView as (
5 select Ra.Val as n,Rb.Val as r
6 from Renban Ra,Renban Rb
7 where Ra.Val >= Rb.Val),
8 RowList as (
9 select n,r,
10 case when r=0 then 1
11 else (select round(exp(sum(Ln(a.N-b.R+1)))) from WorkView b
12 where b.N=a.N
13 and b.R > 0
14 and b.R<=a.R) end as NPR,
15 case when r=0 then 1
16 else (select round(exp(sum(Ln(b.R)))) from WorkView b
17 where b.N=a.N
18 and b.R > 0
19 and b.R<=a.R) end as "R!"
20 from WorkView a),
21 PascalTriangle as (
22 select N,SubStr(sys_connect_by_path(Val,' '),2) as Pascal
23 from (select N,R,NPR / "R!" as Val from RowList)
24 where Connect_by_IsLeaf = 1
26 connect by prior N=N
27 and prior R=R-1)
28 select Lpad(' ',(MaxLength-Length(Pascal))/2-1) || Pascal as PASCALS_TRIANGLE
29 from PascalTriangle,(select max(Length(Pascal)) as MaxLength from PascalTriangle);

PASCALS_TRIANGLE
--------------------------------------------------------------------------------
1
1 1
1 2 1
1 3 3 1
1 4 6 4 1
1 5 10 10 5 1
1 6 15 20 15 6 1
1 7 21 35 35 21 7 1
1 8 28 56 70 56 28 8 1
1 9 36 84 126 126 84 36 9 1
1 10 45 120 210 252 210 120 45 10 1
1 11 55 165 330 462 462 330 165 55 11 1
1 12 66 220 495 792 924 792 495 220 66 12 1
1 13 78 286 715 1287 1716 1716 1287 715 286 78 13 1
1 14 91 364 1001 2002 3003 3432 3003 2002 1001 364 91 14 1
• Member Posts: 116

Query Your Dream & Future at
http://www.soqool.com
• Member Posts: 984 Green Ribbon
iterative power of model:
SQL> var p number;
SQL> exec :p:=10;

PL/SQL procedure successfully completed
p
---------
10

SQL>
SQL> select n from (select 1 n from dual)
2   model
3    dimension by (0 dim)
4    measures (n, n n2)
5     rules iterate(1000) until (iteration_number+1>=:p)
6      (n2[ANY]=n[CV()],
7       n[for dim from 1 to iteration_number increment 1] = nvl(n2[CV()]+n2[CV()-1],1))
8  /

N
----------
1
9
36
84
126
126
84
36
9
1

10 rows selected
p
---------
10

SQL>
• Member Posts: 1,057 Bronze Badge
edited May 28, 2007 11:00AM
Here it is.

Some needed stuff.
create or replace type string_table is table of varchar2(4000)
/

CREATE OR REPLACE
FUNCTION tab2string ( tab in string_table, delimitatore in varchar2 default ',' ) return varchar2 as
out_string varchar2(32767);
begin
if ( cardinality(tab) > 0 ) then
for i in tab.first .. tab.last loop
if ( i != tab.first ) then
out_string := out_string||delimitatore;
else
out_string := '';
end if;
out_string := out_string || tab(i);
end loop;
return out_string;
else
return null;
end if;
end;
/

create or replace function fact(
n in integer
) return integer
as
out_val number;
begin
out_val := 1;
for i in 1 .. n loop
out_val := out_val * i;
end loop;
return out_val;
end;
/
and some output ( n = 6 )
Processing ...
select fact(:n)/(fact(rownum-1)*fact(:n-rownum+1)) val
from dual
connect by rownum-1 <= :n

Query finished, retrieving results...
VAL
--------------------------------------
1
6
15
20
15
6
1

7 row(s) retrieved

Processing ...
select tab2string( cast (
multiset (
select fact(a.row_no)/(fact(level-1)*fact(a.row_no-level+1)) val
from dual
connect by level-1 <= a.row_no
)
as string_table
),' '
) as pascal_triangle
from (
select rownum-1 as row_no
from dual
connect by level-1 <= :n
) a

Query finished, retrieving results...
PASCAL_TRIANGLE
--------------------------------------------------------------------------------
1
1 1
1 2 1
1 3 3 1
1 4 6 4 1
1 5 10 10 5 1
1 6 15 20 15 6 1

7 row(s) retrieved
Bye Alessandro
• Member Posts: 8,424 Bronze Crown

And of course a XML solution:
michaels>  COLUMN pascal format a30

michaels>  VAR pascal number

michaels>  EXEC :pascal := 7

michaels>  SELECT RTRIM(x.COLUMN_VALUE.EXTRACT('r/text()'),',') pascal
FROM XMLTable('declare function local:fact(\$i)
{
if(\$i > 0) then
number(\$i * local:fact(\$i - 1))
else (1)
};

for \$n in 0 to .
return <r> {for \$k in 0 to \$n
return concat(local:fact(\$n) div (local:fact(\$k) * local:fact(\$n - \$k)),",")
}
</r>' PASSING XMLTYPE('<p>'||:pascal||'</p>')) x

PASCAL
------------------------------
1
1, 1
1, 2, 1
1, 3, 3, 1
1, 4, 6, 4, 1
1, 5, 10, 10, 5, 1
1, 6, 15, 20, 15, 6, 1
1, 7, 21, 35, 35, 21, 7, 1

8 rows selected.
• Member Posts: 487
exec :l_numrows := 5;

with data as
(
select ver,hor,decode(num,0,to_number(null),num) numn
from (select level hor from dual connect by level < :l_numrows+2)
, (select level ver from dual connect by level < :l_numrows+1)
model
dimension by (hor,ver)
measures (cast (null as number(10)) num)
rules
(
num[1,any] = 1
, num[2,1] = 1
, num[hor>1,ver>1] = nvl(num[cv(hor)-1,cv(ver)-1],0) + nvl(num[cv(hor),cv(ver)-1],0)
)
)
select ver,totalrow,sumrow
from
(
select *
from data
model
partition by (ver)
dimension by (hor)
measures (numn
,cast(null as varchar2(25)) totalrow
,count(*) over (partition by ver) counter
,cast(0 as number(5)) sumrow)
rules
(
totalrow[any] order by hor = totalrow[cv(hor)-1] ||' '||numn[cv(hor)]
, sumrow[any] order by hor = nvl(sumrow[cv(hor)-1],0) + nvl(numn[cv(hor)],0)
)
)
where hor=counter
order by ver,totalrow;

VER TOTALROW SUMROW
---------- ------------------------- ----------
1 1 1 2
2 1 2 1 4
3 1 3 3 1 8
4 1 4 6 4 1 16
5 1 5 10 10 5 1 32
This discussion has been closed.