How to create this special counter for a specific value in T-SQL?

Question

I have a problem that I struggled with for hours. I am trying to create a special counter from a column from a temporary table, there are a lot of columns in my table, one of them is col1:

col1 |
######
X    | 
X    | 
A    | 
B    | 
X    | 
C    | 
X    | 
D    | 

The special counter should look like this:

col1 | counter
###############
X    | 1
X    | 2
A    | 2
B    | 2
X    | 3
C    | 3
X    | 4
D    | 4

it should only read the value of "X" and not change it if the value was something else.

I tried a lot of things, the closest I got was by creating another temp table only with a counter, and then appending it to the original, but the result was like this:

col1 | counter
###############
X    | 1
X    | 2
A    | NULL
B    | NULL
X    | 3
C    | NULL
X    | 4
D    | NULL

So how can I create this special counter?

Answer 1

Row_Number() CASE .

, .

Declare @YourTable table (ID int,col1 varchar(25))
Insert Into @YourTable values
(1,'X'),
(2,'X'),
(3,'A'),
(4,'B'),
(5,'X'),
(6,'C'),
(7,'X'),
(8,'D')

Select ID,Col1
      ,Counter = max(counter) over (Order By ID)
 From (
       Select ID
             ,col1
             ,counter = case when col1='X' then row_number() over (Partition By col1 Order by ID) else null end
        From  @YourTable
      ) A
 Order By ID

ID  Col1    Counter
1   X       1
2   X       2
3   A       2
4   B       2
5   X       3
6   C       3
7   X       4
8   D       4

Answer 2

. , X, .

declare @counter int = 0;
update   #temp
set      counter = @counter
       , @counter += case when col1 = 'X' then 1
                          else 0
                     end;

Answer 3

There is a much simpler and end-to-end solution. We just need a little observation: the counter is equal to the count Xin the previous lines:

select id, 
       col1,
       (select count(*) from @t where id <= t.id and col1 = 'X') as counter
from @t t