Island and Gaps

create table #tmp_test_data
(
   row char(1)
   ,seat_no char(3)
   ,vacant char(1)
)

insert into #tmp_test_data
select 'A', 'A01', 'Y' UNION
select 'A', 'A02', 'Y' UNION
select 'A', 'A03', 'N' UNION
select 'A', 'A04', 'N' UNION
select 'A', 'A05', 'Y' UNION
select 'A', 'A06', 'Y' UNION
select 'A', 'A07', 'N' UNION
select 'A', 'A08', 'N' UNION
select 'A', 'A09', 'Y' UNION
select 'A', 'A10', 'N' UNION
select 'A', 'A11', 'Y' UNION
select 'A', 'A12', 'Y' UNION
select 'A', 'A13', 'Y' 

with cte1
as
(
    select *
          ,dense_rank() over(partition by row order by seat_no) as c_dr
          ,dense_rank() over(partition by row, vacant order by seat_no) as v_dr
          , dense_rank() over(partition by row order by seat_no) - dense_rank() over(partition by row, vacant order by seat_no) as grp
    from #tmp_test_data
),
cte2
as
(
    select Grp
           ,count(grp) as av_unav_cnt
            ,vacant
    from cte1
    group by grp, vacant
)
select row
        ,min(seat_no) as seat_no_start
        ,max(seat_no) as seat_no_end
        ,av_unav_cnt
        ,a.vacant
from cte1 a
join cte2 b on a.grp = b.grp and a.vacant = b.vacant
group by row, a.grp, av_unav_cnt,a.vacant