1

How to Generate Fibonacci series in sql !

I need to generate Fibonacci series 0 1 1 2 3 5 8 13 21 ... N

I did this easily using C-code I need to do this using Sql !

  • This is one crazy Homework given ! to torture students ! @JonathonReinhart –  Feb 13 '14 at 06:09
  • Also, StackOverflow isn't here to complete your homework for you. – mbomb007 Dec 18 '14 at 21:12
  • 1
    Question is Off-Topic according to StackOverflow guidelines: "Questions asking for homework help must include a summary of the work you've done so far to solve the problem, and a description of the difficulty you are having solving it." – mbomb007 Dec 18 '14 at 21:15

6 Answers6

16

Try This Simple Query:

1) For Result In Row-by-Row (Single Column, Multiple Rows)

WITH Fibonacci (PrevN, N) AS
(
     SELECT 0, 1
     UNION ALL
     SELECT N, PrevN + N
     FROM Fibonacci
     WHERE N < 1000000000
)
SELECT PrevN as Fibo
     FROM Fibonacci
     OPTION (MAXRECURSION 0);

Output 1:

enter image description here

2) For Result in Only One Row (Comma sepreted, in Single Cell)

WITH Fibonacci (PrevN, N) AS
(
 SELECT 0, 1
    UNION ALL
    SELECT N, PrevN + N
    FROM Fibonacci
    WHERE N < 1000000000
)
SELECT Substring(
    (SELECT cast(', ' as varchar(max)) + cast(PrevN as varchar(max)
);
FROM Fibonacci
FOR XML PATH('')),3,10000000) AS list

Output 2: enter image description here

124
  • 2,757
  • 26
  • 37
4

Please try:

SELECT 0  AS fib_number UNION ALL
SELECT
    FLOOR(POWER(( 1 + SQRT(5) ) / 2.0, number) / SQRT(5) + 0.5)
FROM master..spt_values
WHERE TYPE = 'p' AND number BETWEEN 1 AND 70
TechDo
  • 18,398
  • 3
  • 51
  • 64
4

Try this !

declare @a int
declare @b int
declare @c int
Declare @i int

set @a=0
set @b=1
set @i=0
set @c=0
Print 'Fibonacci Series'
print @a
print @b
while @i<10 
Begin
set @c=@a+@b
print @c
set @i=@i+1
set @a=@b
set @b=@c
end

Outputs !

Fibonacci Series

0 1 1 2 3 5 8 13 21 34 55 89

vhadalgi
  • 7,027
  • 6
  • 38
  • 67
3

Try this :-

 Declare @Fib int = 5
 ;with cte as
 (
   Select 0 as Previous,1 as Next ,0 as Level 
   union all
   Select Next,Next + Previous,Level +1 from cte 
   where Level +1 <@Fib
 )
 Select Previous as FibonacciSeries from cte
praveen
  • 12,083
  • 1
  • 41
  • 49
0

This is for generating the first 10 numbers in the series.

DECLARE @NoOne INT,  @NoTwo INT
DECLARE @FibonacciTable TABLE (Number INT NOT NULL)
--Insert first two numbers 0 and 1
INSERT @FibonacciTable (Number) SELECT 0 UNION ALL SELECT 1
SELECT @NoOne = 0, @NoTwo = 1

WHILE (SELECT COUNT(*) FROM @FibonacciTable) < 10
BEGIN
    INSERT @FibonacciTable (Number) VALUES(@NoOne + @NoTwo)
    SELECT @NoTwo = @NoOne + @NoTwo, @NoOne = @NoTwo - @NoOne
END
SELECT * FROM @FibonacciTable   
GO
MusicLovingIndianGirl
  • 5,909
  • 9
  • 34
  • 65
0
CREATE TABLE #Fibonacci (value BIGINT)
GO

INSERT INTO #Fibonacci(value) SELECT 0
INSERT INTO #Fibonacci(value) SELECT 1
SELECT * FROM #Fibonacci
GO

INSERT INTO #Fibonacci(value) 
SELECT SUM(value) FROM ( SELECT TOP 2 * FROM #Fibonacci ORDER BY value DESC ) AS value
GO 10   --  Loop insert 10 value...

SELECT * FROM #Fibonacci
DROP TABLE #Fibonacci

Result Code

muhammed sümer
  • 1
  • 2