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;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi