计算每行有数据的列数 [英] count number of columns that have data for each row

问题描述

我有 8 列和 5000 多行的表格.

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

我需要帮助编写 t-sql select 来计算每行不为空的列数.

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

推荐答案

试试这个，用你的表名替换两次出现的Catalog".这已在默认 ReportServer 数据库中的 SQL Server 2008 R2 Dev Edition 上成功测试.

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

请注意，如果您不能信任列名称的来源(例如，如果最终用户可以使用他们控制的名称创建列)，则此方法容易受到 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).

这篇关于计算每行有数据的列数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！