locked
Adding Numbers together RRS feed

  • Question

  • Hi there I am trying to create the following in SQL automatically. 

    Step needs to double itself each time.  How would I write that one out ? Yes I would have the first number as 1. 

    ID Step 
    1 1
    2 2
    3 4
    4 8
    5 16
    6 32
    7 64
    8 128
    9 256
    10 512
    Monday, May 18, 2015 2:28 AM

Answers

  • are you looking for this?
    CREATE TABLE #temp
        ([ID] int, [Step] int)
    ;
        
    INSERT INTO #temp
        ([ID], [Step])
    VALUES
        (1, 1),
        (2, 2),
        (3, 4),
        (4, 8),
        (5, 16),
        (6, 32),
        (7, 64),
        (8, 128),
        (9, 256),
        (10, 512)
    ;
    
    
    
    select id, power(2,id-1),step from #temp


    Satheesh
    My Blog | How to ask questions in technical forum

    • Proposed as answer by Jingyang Li Monday, May 18, 2015 12:03 PM
    • Marked as answer by Eric__Zhang Monday, June 1, 2015 8:04 AM
    Monday, May 18, 2015 3:00 AM
  • How about this 

    create table #temp(c1 int , c2 as power(2,c1-1))
    
    insert into #temp(c1) values(1),
    (2),
    (3),
    (4),
    (5),
    (6),
    (7),
    (8),
    (9),
    (10)
    
    select * from #temp

    OR

    create table #temp(c1 int identity(1,1), c2 as power(2,c1-1))
    go
    insert into #temp default values 
    go 10
    
    select * From #temp


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Eric__Zhang Monday, June 1, 2015 8:07 AM
    Monday, May 18, 2015 12:23 PM
  • Hi Cr_banks,

    Here's another approach with recursion.

    ;WITH Cte AS
    (
    SELECT 1 AS ID, CAST(1 AS decimal(38,0)) AS Step
    UNION ALL
    SELECT ID+1, Step*2 FROM Cte
    WHERE ID< 100
    )
    SELECT * FROM Cte
    OPTION(MAXRECURSION 0)

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support


    • Proposed as answer by Eric__Zhang Tuesday, May 26, 2015 2:09 AM
    • Marked as answer by Eric__Zhang Monday, June 1, 2015 8:05 AM
    Tuesday, May 19, 2015 2:12 AM

All replies

  • are you looking for this?
    CREATE TABLE #temp
        ([ID] int, [Step] int)
    ;
        
    INSERT INTO #temp
        ([ID], [Step])
    VALUES
        (1, 1),
        (2, 2),
        (3, 4),
        (4, 8),
        (5, 16),
        (6, 32),
        (7, 64),
        (8, 128),
        (9, 256),
        (10, 512)
    ;
    
    
    
    select id, power(2,id-1),step from #temp


    Satheesh
    My Blog | How to ask questions in technical forum

    • Proposed as answer by Jingyang Li Monday, May 18, 2015 12:03 PM
    • Marked as answer by Eric__Zhang Monday, June 1, 2015 8:04 AM
    Monday, May 18, 2015 3:00 AM
  • Yep that's the one.  Thanks for that.
    Monday, May 18, 2015 11:10 AM
  • How about this 

    create table #temp(c1 int , c2 as power(2,c1-1))
    
    insert into #temp(c1) values(1),
    (2),
    (3),
    (4),
    (5),
    (6),
    (7),
    (8),
    (9),
    (10)
    
    select * from #temp

    OR

    create table #temp(c1 int identity(1,1), c2 as power(2,c1-1))
    go
    insert into #temp default values 
    go 10
    
    select * From #temp


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Eric__Zhang Monday, June 1, 2015 8:07 AM
    Monday, May 18, 2015 12:23 PM
  • Hi Cr_banks,

    Here's another approach with recursion.

    ;WITH Cte AS
    (
    SELECT 1 AS ID, CAST(1 AS decimal(38,0)) AS Step
    UNION ALL
    SELECT ID+1, Step*2 FROM Cte
    WHERE ID< 100
    )
    SELECT * FROM Cte
    OPTION(MAXRECURSION 0)

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support


    • Proposed as answer by Eric__Zhang Tuesday, May 26, 2015 2:09 AM
    • Marked as answer by Eric__Zhang Monday, June 1, 2015 8:05 AM
    Tuesday, May 19, 2015 2:12 AM