Find groups containing 6 consecutive 1s in one column

I have a table with 2 columns:

• val with values: 0 or 1
• id with unique identifiers

with cte(val, id) as (
    select 0, 0 union all
    select 1, 1 union all
    select 1, 2 union all
    select 0, 3 union all
    select 1, 4 union all
    select 1, 5 union all
    select 1, 6 union all
    select 1, 7 union all
    select 1, 8 union all
    select 1, 9 union all
    select 1, 10
)
select *
into #tmp
from cte

How do I to find id with 6 values = 1 in a row.

In the example above: id = 9, id = 10.

It is desirable not to use loops (cursors or while), but something like sum(...) over(...).

Answer

You can use running sum over a window frame that contains exactly 6 rows (5 prior plus current row):

with cte as (
    select *, sum(val) over (
        order by id
        rows between 5 preceding and current row
    ) as rsum
    from #tmp
)
select *
from cte
where rsum = 6

Adjust the size of the window and where clause to match the desired value.