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

Question

I've problem that I've been struggling with for hours. I'm trying to create a special counter on a column from a temporary table, my table has many columns, one of them is col1:

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

The special counter should be like this:

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

it should count the "X" value only, and keep it unchanged in case the value was anything else.

I tried a lot of things, the closest I got was by creating another temp table with counter only, and then join it with the original one, 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() within a CASE should do the trick.

You'll notice I added a field ID to maintain a sequence.

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

Returns

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

Here it is in an update statement. The statement increments the variable every time an X is encountered and updates every row with the correct value.

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