Number of NULL samples per row

Question

I want to count the number of NULL values ​​in a string.

For example:

 Name Col1 Col2 Col3 Col4 | ansCol abc null [e-mail] null null | 3 bbc null null null null | 4 

In this example, the answer is simple:

 SELECT *, (CASE WHEN Name IS NULL THEN 1 ELSE 0 END + CASE WHEN Col1 IS NULL THEN 1 ELSE 0 END + CASE WHEN Col2 IS NULL THEN 1 ELSE 0 END + CASE WHEN Col3 IS NULL THEN 1 ELSE 0 END + CASE WHEN Col4 IS NULL THEN 1 ELSE 0 END) as ansCol 

The question becomes more complicated when we have a very wide table, more than 10 columns. If I try to use the method that was shown, I get the following error:

Msg 125, Level 15, State 4, Line 13 Case expressions can only be inserted in level 10.

Is there any way to overcome this?

Answer 1

In addition to the above, a potential approach would be to use the functionality of PIVOT and UNPIVOT .

First create a table and paste the data into the question:

 /* Create table */ CREATE TABLE so_40957006 ( [name] VARCHAR(24), [col1] VARCHAR(24), [col2] VARCHAR(24), [col3] VARCHAR(24), [col4] VARCHAR(24) ) /* Insert data */ INSERT INTO so_40957006 VALUES('abc', null, ' [e-mail] ', null, null) INSERT INTO so_40957006 VALUES('bbc', null, null, null, null) 

First, we will create an identification table so that we can uniquely identify each row of data and insert data.

 /* Create identity table */ CREATE TABLE so_40957006_id ( [lid] INT IDENTITY(1, 1), [name] VARCHAR(24), [col1] VARCHAR(24), [col2] VARCHAR(24), [col3] VARCHAR(24), [col4] VARCHAR(24) ) /* Insert data into table */ INSERT INTO so_40957006_id SELECT ISNULL([name], 1) AS [name] ,ISNULL([col1], 1) AS [col1] ,ISNULL([col2], 1) AS [col2] ,ISNULL([col3], 1) AS [col3] ,ISNULL([col4], 1) AS [col4] FROM so_40957006 

Note that in the process, we converted any of the columns that have a null value of 1 using IsNull . Now the table is as follows.

 lid name col1 col2 col3 col4 1 abc 1 [e-mail] 1 1 2 bbc 1 1 1 1 

In doing so, we can now use PIVOT and UNPIVOT to verticalize data. For example, by running the query below

 SELECT [lid], [cols], [val] FROM (SELECT [lid], [name], [col1], [col2], [col3], [col4] FROM so_40957006_id) p UNPIVOT (val FOR cols IN ([name], [col1], [col2], [col3], [col4]) ) AS unpvt 

provides you with vertical output:

 lid cols val 1 name abc 1 col1 1 1 col2 [e-mail] 1 col3 1 1 col4 1 2 name bbc 2 col1 1 2 col2 1 2 col3 1 2 col4 1 

Since the values ​​you want to count (i.e. NULL s) are 1, you can simply run the GROUP BY + SUM statement:

 SELECT [lid], SUM(cast([val] as integer)) as CountNulls FROM ( SELECT [lid], [cols], [val] FROM (SELECT [lid], [name], [col1], [col2], [col3], [col4] FROM so_40957006_id) p UNPIVOT (val FOR cols IN ([name], [col1], [col2], [col3], [col4]) ) AS unpvt ) a WHERE ISNUMERIC([val]) = 1 GROUP BY [lid] 

with exit

 lid CountNulls 1 3 2 4 

With [lid] you can JOIN return this result to the original table to generate your result set.

Answer 2

The code in your question does not have a nested case, so you should be able to combine more than 10 with the addition just fine.

But some other possibilities ...

If all columns have the same data type that you could use ( Demo )

 SELECT *, ansCol = (SELECT COUNT(*) - COUNT(C) FROM (VALUES(c1),(c2),(c3),(c4)) V(C)) FROM t1 

Or another alternative that does not rely on them with the same data type.

 ;WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' as ns) SELECT *, ansCol = (SELECT x.* FOR XML PATH('row'), ELEMENTS XSINIL, TYPE).value('count(/row/*[@ns:nil])', 'int') FROM t1 x 

Answer 3

This seems like a hack, and maybe you need to change the design or reduce the number of columns in the table, but if you have to do this, one option would be to use a subquery to get around the 10 column limit:

 SELECT t.*, t.firstBatch + t.secondBatch AS ansCol FROM ( SELECT *, (CASE WHEN Col1 IS NULL THEN 1 ELSE 0 END + CASE WHEN Col2 IS NULL THEN 1 ELSE 0 END + ... CASE WHEN Col10 IS NULL THEN 1 ELSE 0 END) AS firstBatch, (CASE WHEN Col11 IS NULL THEN 1 ELSE 0 END + CASE WHEN Col12 IS NULL THEN 1 ELSE 0 END + ... CASE WHEN Col20 IS NULL THEN 1 ELSE 0 END) AS secondBatch FROM yourTable ) t