count number of columns that have data for each ro

问题:

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

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

回答1:

Try this, replacing the two occurrences of "Catalog" with the name of your table. This has been tested successfully on 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 a SQL-Injection attack if you can't trust the source of the column names (e.g. if end users can causes columns to be created with names under their control).

回答2:

Pure Transact SQL (not using dynamic SQL call which 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