Make geometric sequence from series of bit values

Question

I have this table:

declare @Table table (value int)  
insert @Table select 0  
insert @Table select 1  
insert @Table select 1  
insert @Table select 1  
insert @Table select 0  
insert @Table select 1  
insert @Table select 1  

Now, I need to make a Select query, which would add a column. This column will make a geometric sequence once there is a serie of value 1 in column value.

This would be the result:

Answer 1

I think that you need a column that specifies the order of these values, otherwise the order is undefined. The next possible approach uses an additional id column:

declare @Table table (id int primary key, value int)
insert @Table select 1, 0
insert @Table select 3, 1
insert @Table select 4, 1
insert @Table select 7, 1
insert @Table select 8, 0
insert @Table select 14, 1
insert @Table select 17, 1

;
with Q1 as
(
    select *, isnull(lag(value) over (order by id), 0) as prev
    from @Table
),
Q2 as
(
    select *, (select max(id) from Q1 where id <= q.id and prev = 0) as mx
    from Q1 as q
),
Q3 as
(
    select *, (select count(id) from Q2 where id <= q.id and mx = q.mx) as c
    from Q2 as q
) 
select value, case value when 1 then POWER(2, c) else 0 end as GeometricSequence
from Q3
order by id

There are alternatives, though. Maybe show your attempts too.

Answer 2

taken from my question at stackoverflow.com, solution by Gordon Linoff

select t.*,
       value * power(2, row_number() over (partition by grp order by id) - 1) as generatedsequence
from (select t.*, sum(1 - value) over (order by id) as grp
      from @table t
     ) t;