SELECT U.Name,

U.Value

FROM (

SELECT C1Count = COUNT(ASCII(C1)),

C2Count = COUNT(ASCII(C2)),

C3Count = COUNT(ASCII(C3)),

C4Count = COUNT(NULLIF(C4, 0))

FROM #test_data_table

) S

UNPIVOT (

Value

FOR Name

IN (C1Count, C2Count, C3Count, C4Count)

) U;