SQL Server 2022

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

CREATE TABLE Test
(
  id int identity(1, 1) PRIMARY KEY
, a int NULL
, b int NULL
, c int NULL
);
INSERT INTO Test (a, b, c)
VALUES
  (1, 1, 1)
, (2, NULL, 2)
, (NULL, 3, 3)
, (NULL, 4, NULL)
, (5, NULL, 5)
, (NULL, 6, 6);
6 rows affected

SELECT *
FROM Test;


id
a
b
c


1
1
1
1

2
2
null
2

3
null
3
3

4
null
4
null

5
5
null
5

6
null
6
6


SELECT col
  , COUNT(*) AS non_NULLs
  , (SELECT COUNT(*) AS all_rows FROM Test) - COUNT(*) AS NULLs
FROM 
  Test
UNPIVOT
(
  val
  FOR col
  IN ([a], [b], [c])
) u
GROUP BY u.col


col
non_NULLs
NULLs


a
3
3

b
4
2

c
5
1

id	a	b	c
1	1	1	1
2	2	null	2
3	null	3	3
4	null	4	null
5	5	null	5
6	null	6	6