-1

I'm using SQL Server 14 and I need to count the number of null values in a row to create a new column where a "% of completeness" for each row will be stored. For example, if 9 out of 10 columns contain values for a given row, the % for that row would be 90%.

I know this can be done via a number of Case expressions, but the thing is, this data will be used for a live dashboard and won't be under my supervision after completion.

I would like for this % to be calculated every time a function (or procedure? not sure what is used in this case) is run and need to know the number of columns that exist in my table in order to count the null values in a row and then divide by the number of columns to find the "% of completeness".

Any help is greatly appreciated!

Thank you

GMB
  • 216,147
  • 25
  • 84
  • 135
Guillermo Garcia
  • 29
  • 4

1 Answers1

1

One method uses cross apply to unpivot the columns to rows and count the ratio of non-null values.

Assuming that your table has columns col1 to col4, you would write this as:

select t.*, x.*
from mytable t
cross apply (
    select avg(case when col is not null then 1.0 else 0 end) completeness_ratio
    from (values (col1), (col2), (col3), (col4)) x(col)
) x
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thank you for your answer. I like your approach, but today I know how many columns I have in my table, but I might not know exactly how many I have tomorrow. What I mean is an additional column might be added to the table, and I want to prepare for this. Still not sure if there even is a way to prepare for this. – Guillermo Garcia Sep 11 '20 at 01:05
  • 1
    @GuillermoGarcia You probably will have to use [Dynamic SQL][1] and utilize [DMV][2] to get the current list of all of the columns, if this answer works for your need then I suspect it can be transformed to use dynamic SQL? [1]: https://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/ [2]: https://stackoverflow.com/questions/1054984/how-can-i-get-column-names-from-a-table-in-sql-server – Pete -S- Sep 11 '20 at 02:03