SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Records
(
  StationId INT,
  Col1 VARCHAR(255),
  Col2 VARCHAR(255)
)
INSERT INTO Records
VALUES (1, 'Col1 Value', NULL),
  (1, NULL, 'Col2 Value'),
  (1, NULL, NULL),
  (2, 'Col1 Value', NULL),
  (2, NULL, 'Col2 Value'),
  (2, 'Col1 Value', 'Col2 Value')
6 rows affected

SELECT * FROM Records


StationId
Col1
Col2


1
Col1 Value
null

1
null
Col2 Value

1
null
null

2
Col1 Value
null

2
null
Col2 Value

2
Col1 Value
Col2 Value


SELECT StationId,
  SUM(Value)
FROM
(
SELECT StationId,
  CASE
    WHEN Col1 IS NOT NULL OR Col2 IS NOT NULL THEN 1
    ELSE 0
  END AS Value
FROM Records
) g
GROUP BY StationId


StationId
(No column name)


1
2

2
3

StationId	Col1	Col2
1	Col1 Value	null
1	null	Col2 Value
1	null	null
2	Col1 Value	null
2	null	Col2 Value
2	Col1 Value	Col2 Value