IF OBJECT_ID('tempdb..#test_data_table','U') IS NOT NULL DROP TABLE #test_data_table
IF OBJECT_ID('tempdb..#test_count_table','U') IS NOT NULL DROP TABLE #test_count_table

SELECT 'Data' C1,'Data' C2,'Data' C3,1 C4 INTO #TEST_DATA_TABLE UNION ALL
SELECT 'Data','Data','Data',1 UNION ALL
SELECT 'Data','','Data',0 UNION ALL
SELECT 'Data','Data','',1 UNION ALL
SELECT '','Data','Data',0 UNION ALL
SELECT '','','Data',1 UNION ALL
SELECT '','Data','',1 UNION ALL
SELECT '','','',0

--One way (creates a result table)
SELECT
SUM(CASE WHEN C1!='' THEN 1 ELSE 0 END) C1Count,
SUM(CASE WHEN C2!='' THEN 1 ELSE 0 END) C2Count,
SUM(CASE WHEN C3!='' THEN 1 ELSE 0 END) C3Count,
SUM(CASE WHEN C4!=0 THEN 1 ELSE 0 END) C4Count
INTO #test_count_table
FROM #test_data_table

SELECT 'C1Count',C1Count
FROM #test_count_table
UNION
SELECT 'C2Count',C2Count
FROM #test_count_table
UNION
SELECT 'C3Count',C3Count
FROM #test_count_table
UNION
SELECT 'C4Count',C4Count
FROM #test_count_table


--Another way (direct)
SELECT 'C1Count',COUNT(C1)
FROM #test_data_table
WHERE C1!=''
UNION
SELECT 'C2Count',COUNT(C2)
FROM #test_data_table
WHERE C2!=''
UNION
SELECT 'C3Count',COUNT(C3)
FROM #test_data_table
WHERE C3!=''
UNION
SELECT 'C4Count',COUNT(C4)
FROM #test_data_table
WHERE C4!=0

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;