SQL Server 2022

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

DECLARE @demo TABLE
(
Col1 INT NULL,
Col2 INT NULL,
Col3 INT NULL
)

INSERT @demo 
VALUES 
(1,2,NULL),
(1,2,NULL),
(1,NULL,NULL);

select
     avg((case when Col1 is null then 100.00 else 0 end)) Col1_null_prct
    ,avg((case when Col2 is null then 100.00 else 0 end)) Col2_null_prct
    ,avg((case when Col3 is null then 100.00 else 0 end)) Col3_null_prct
from @demo;

WITH T AS
(
select
     avg((case when Col1 is null then 100.00 else 0 end)) Col1_null_prct
    ,avg((case when Col2 is null then 100.00 else 0 end)) Col2_null_prct
    ,avg((case when Col3 is null then 100.00 else 0 end)) Col3_null_prct
from @demo
)
SELECT col, val
FROM T 
UNPIVOT (val FOR col IN (Col1_null_prct, Col2_null_prct, Col3_null_prct)) U;

WITH T1 AS
(
select
     avg((case when Col1 is null then 100.00 else 0 end)) Col1_null_prct
    ,avg((case when Col2 is null then 100.00 else 0 end)) Col2_null_prct
    ,avg((case when Col3 is null then 100.00 else 0 end)) Col3_null_prct
from @demo
)
SELECT col = n.value('local-name(.)', 'sysname'), 
       val = n.value('./text()[1]', 'numeric(5,2)')
FROM (SELECT * FROM T1 FOR XML PATH('row'), TYPE) T2(x)
CROSS APPLY x.nodes('row/*') n(n);

Col1_null_prct	Col2_null_prct	Col3_null_prct
0.000000	33.333333	100.000000

col	val
Col1_null_prct	0.000000
Col2_null_prct	33.333333
Col3_null_prct	100.000000

col	val
Col1_null_prct	0.00
Col2_null_prct	33.33
Col3_null_prct	100.00