Select rows where 'A1' is followed by 'A2'
Since you don't mention any specific DBMS I'll assume first that there are no window functions:

select A.Id, A.ColA, B.Id, B.ColA
from T as A
join T as B
    on A.Id + 1 = B.Id
where A.ColA = 'A1'
  and B.ColA = 'A2'


Assuming that window functions do exists:

select Id, ColA, Lead_id, LeadColA
from (
    select Id, ColA
         , Lead(id) over ( order by Id ) as Lead_Id
         , Lead(ColA) over ( order by Id ) as Lead_ColA
    from T
)
where Id + 1 = Lead_id
  and ColA = 'A1'
  and Lead_ColA = 'A2'


All untested, but you should get the idea

An alternative to @Vérace suggestion for a more tabular format is to use a lateral cross join that transpose the result. I.e.:

select X.Id, X.ColA
from T as A
join T as B
    on A.Id + 1 = B.Id 
cross join lateral ( values (A.Id, A.ColA)
                          , (B.Id, B.ColA) 
                   ) X (id, ColA)
where A.ColA = 'A1'
  and B.ColA = 'A2'


For SQL server I believe cross apply can be used in the same way. DB2, Oracle, Postgres all implement LATERAL, MySQL does not. The same technique can be applied with window functions as well:

select x.Id, x.ColA
from (
    select Id, ColA
         , Lead(id) over ( order by Id ) as Lead_Id
         , Lead(ColA) over ( order by Id ) as Lead_ColA
    from T
) as Y cross join lateral ( 
               values (y.id, y.cola)
                    , (y.lead_id, y.lead_cola) 
) as X (id, cola)
where y.Id + 1 = y.Lead_id
  and y.ColA = 'A1'
  and y.Lead_ColA = 'A2'