Counting Null Values in a Row
On StackOverflow today someone asked how to get the number of null values in a table’s row; i.e. if there are 10 columns, and 4 of the columns contain data, the answer would be 6.
The requirement was that it would work in sqlite, which I don’t have a generic solution for, but it did prompt me to play around with this in SQL Server. The result is fairly tidy:
with xmltab(xmlcol) as (select cast( (select * from myTable where myIdCol = 1 for xml path('MYTABLE'), elements xsinil ) as xml) as xmlcol ) select cast(cast(xmlcol.query('count(//*[@xsi:nil="true"])') as varchar) as int) from xmltab