Count columns until the value changes

You are looking for a CASE expression:

SELECT
  t.*,
  CASE
    WHEN col1 <> col2 THEN 1
    WHEN col2 <> col3 THEN 2
    WHEN col3 <> col4 THEN 3
    WHEN col4 <> col5 THEN 4
    WHEN col5 <> col6 THEN 5
    WHEN col6 <> col7 THEN 6
    ELSE                   7
  END AS consistent_columns
FROM mytable t
ORDER BY id;

Here is a generic solution based on SQL Server's XML and XQuery functionality.

Table column names and their count is irrelevant.

Data sample was created verbatim as given in the question.

Notable points:

• 1st CROSS APPLY is composing XML for each row.
• 2nd CROSS APPLY is using XQuery FLWOR expression to get position of the first XML element that is different from the very first XML element, i.e. consistent columns in the OP parlance.
• 3rd CROSS APPLY is counting all columns to cover a scenario where all values are identical in all columns for a given row.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Col1 CHAR(1), Col2 CHAR(1), Col3 CHAR(1), Col4 CHAR(1), Col5 CHAR(1), Col6 CHAR(1), Col7 CHAR(1));
INSERT @tbl (Col1, Col2, Col3, Col4, Col5, Col6, Col7) VALUES
('A','A','X','A','A','A','A'),
('A','A','A','A','X','A','A'),
('X','X','X','A','X','A','A'),
('A','A','A','A','A','A','A');
-- DDL and sample data population, end

SELECT t.*, x
    , ConsistentColumns = COALESCE(FirstShot, total)
FROM @tbl AS t
CROSS APPLY (SELECT t.* FOR XML PATH(''), TYPE, ROOT('root')) AS t1(x)
CROSS APPLY (SELECT x.query('let $first := /root/*[position() gt 1][1]/text()
        for $x in /root/*[position() gt 1]
        let $pos := count(root/*[. << $x]) 
        where $x/text() != $first
        return <pos>{$pos}</pos>
    ').value('(pos/text())[1]', 'int') - 1) AS t2(FirstShot)
CROSS APPLY (SELECT x.query('count(/root/*[position() gt 1])').value('.','int')) AS t3(total);

Output

One option to do it is to use consequtive left joins and count concatenated values.

Select 
   t1.*, 
  LEN(CONCAT(t1.col1, t2.col2, t3.col3, t4.col4, t5.col5, t6.col6, t7.col7)) as CNT
From      TBL t1
Left join TBL t2 ON(t2.ID = t1.ID And t1.col1 = t2.col2)
Left join TBL t3 ON(t3.ID = t2.ID And t2.col2 = t3.col3)
Left join TBL t4 ON(t4.ID = t3.ID And t3.col3 = t4.col4)
Left join TBL t5 ON(t5.ID = t4.ID And t4.col4 = t5.col5)
Left join TBL t6 ON(t6.ID = t5.ID And t5.col5 = t6.col6)
Left join TBL t7 ON(t7.ID = t6.ID And t6.col6 = t7.col7)

This is how it looks without concatination:

Select 
   t1.ID, 
   t1.col1, t2.col2, t3.col3, t4.col4, t5.col5, t6.col6, t7.col7
From      TBL t1
Left join TBL t2 ON(t2.ID = t1.ID And t1.col1 = t2.col2)
Left join TBL t3 ON(t3.ID = t2.ID And t2.col2 = t3.col3)
Left join TBL t4 ON(t4.ID = t3.ID And t3.col3 = t4.col4)
Left join TBL t5 ON(t5.ID = t4.ID And t4.col4 = t5.col5)
Left join TBL t6 ON(t6.ID = t5.ID And t5.col5 = t6.col6)
Left join TBL t7 ON(t7.ID = t6.ID And t6.col6 = t7.col7)

fiddle

Generic example.

1. Convert row to JSON ( replace((SELECT t.* FOR JSON PATH),',','},{'))
   and then to JSON ARRAY

[{"Id":1},{"Col1":"A"},{"Col2":"A"},{"Col3":"X"},{"Col4":"A"},{"Col5":"A"},{"Col6":"A"},{"Col7":"A"}]   

2. Extract RowJson values to rowset of JSON - OpenJson(RowJson) ->ColumnJson

"{"Id":1}"
"{"Col1":"A"}"
"{"Col2":"A"}"

3. Extract column JSON to key-value pair. Openjson(columnJson.value)
4. Specially for first (not Id) column

openjson(json_query(rowJson,'$[1]'))c1 -- column1 name and value

5. Take key of first row where "value<>Column1Value" and count of columns.

Test data

CREATE TABLE test ( Id  INT, Col1   VARCHAR(5), Col2    VARCHAR(5),
    Col3    VARCHAR(5), Col4    VARCHAR(5), Col5    VARCHAR(5),
    Col6    VARCHAR(5),   Col7  VARCHAR(5));

INSERT INTO test (Id, Col1, Col2, Col3, Col4, Col5, Col6, Col7) VALUES
    ('1', 'A', 'A', 'X', 'A', 'A', 'A', 'A'),
    ('2', 'A', 'A', 'A', 'A', 'X', 'A', 'A'),
    ('3', 'X', 'X', 'X', 'A', 'X', 'A', 'A'),
    ('4', 'A', 'A', 'A', 'A', 'A', 'A', 'A');

Query

select a.id,coalesce(difcolNum,lastColnum) ConsistentColumns
from(
  select id ,replace((SELECT t.* FOR JSON PATH),',','},{') rowJson
  from test t
)a
cross apply (
  select min(case when cast(columnJson.[key] as int)>0 
          and cast(KV.value as varchar(5))<>c1.[value] then cast(columnJson.[key] as int) end) difColnum
     ,max(cast(columnJson.[key] as int)) lastColnum
  from Openjson(rowJson) as columnJson  -- {"Col1":"A"},{"Col2":"A"}
  cross apply Openjson(columnJson.value) as KV -- KeyValue [key]="Col3"[value]="X"
  cross apply openjson(json_query(rowJson,'$[1]'))c1 -- for 1-st column KeyValue [key]="Col1"[value]="A"
 )b

Fiddle

The task is to count how many consecutive columns from the first one have the same value until it changes.

Since SQL Server does not allow dynamic column iteration, the correct approach is:

1. Convert columns into rows

2. Preserve column order

3. Compare against the first column

4. Stop counting when the value changes

SELECT
    t.Id,
    t.Col1, t.Col2, t.Col3, t.Col4, t.Col5, t.Col6, t.Col7,
    COUNT(*) AS ConsistentColumns
FROM T t
CROSS APPLY (
    VALUES
        (1, t.Col1),
        (2, t.Col2),
        (3, t.Col3),
        (4, t.Col4),
        (5, t.Col5),
        (6, t.Col6),
        (7, t.Col7)
) v(ColOrder, ColValue)
WHERE v.ColValue = t.Col1
  AND v.ColOrder <= (
        SELECT MIN(v2.ColOrder)
        FROM (
            VALUES
                (1, t.Col1),
                (2, t.Col2),
                (3, t.Col3),
                (4, t.Col4),
                (5, t.Col5),
                (6, t.Col6),
                (7, t.Col7)
        ) v2(ColOrder, ColValue)
        WHERE v2.ColValue <> t.Col1
  ) OR NOT EXISTS (
        SELECT 1
        FROM (
            VALUES
                (1, t.Col1),
                (2, t.Col2),
                (3, t.Col3),
                (4, t.Col4),
                (5, t.Col5),
                (6, t.Col6),
                (7, t.Col7)
        ) v3(ColOrder, ColValue)
        WHERE v3.ColValue <> t.Col1
  )
GROUP BY
    t.Id, t.Col1, t.Col2, t.Col3, t.Col4, t.Col5, t.Col6, t.Col7;