The number of data columns for each row

Question

I have a table with 8 columns and 5000+ rows.

I need help writing a t-sql select that would count for each row the number of columns that are not null.

Answer 1

Try this by replacing two occurrences of "Directory" with the name of your table. This was successfully tested in SQL Server 2008 R2 Dev Edition in the default ReportServer database.

DECLARE @Sql nvarchar(max) SET @Sql = 'SELECT 0' SELECT @Sql = @Sql + ' + CASE WHEN [' + [sys].[columns].[name] + '] IS NULL THEN 1 ELSE 0 END' FROM [sys].[columns] WHERE [sys].[columns].[object_id] = OBJECT_ID('Catalog') AND [sys].[columns].is_nullable = 1 SET @Sql = @Sql + ' AS [NullValuesCount] FROM [Catalog]' PRINT @Sql EXEC sp_executesql @Sql 

Note that this approach is susceptible to SQL-Injection attacks if you cannot trust the source of column names (for example, if end users can create columns with names under their control).

Answer 2

Pure Transact SQL (without using a dynamic SQL call that is not part of TSQL):

 SELECT CASE WHEN c1 IS NULL THEN 0 ELSE 1 END + CASE WHEN c2 IS NULL THEN 0 ELSE 1 END + CASE WHEN c3 IS NULL THEN 0 ELSE 1 END + CASE WHEN c4 IS NULL THEN 0 ELSE 1 END + CASE WHEN c5 IS NULL THEN 0 ELSE 1 END + CASE WHEN c6 IS NULL THEN 0 ELSE 1 END + CASE WHEN c7 IS NULL THEN 0 ELSE 1 END + CASE WHEN c8 IS NULL THEN 0 ELSE 1 END FROM T