SQL Query to return rows with the most columns populated

Question

Azure SQL Server 2019.

We have a table Table1 with over 100 columns of differing types of nvarchar data, all of which are allowed NULL values, and where there could be anywhere from 1 to 100 columns populated in a given record. I need to formulate a query that returns the rows ranked by how many columns have values in them, in descending order.

I started going down a road of using DATALENGTH and having to type out the name of every single column, but I can only imagine there has to be a more efficient way. Assuming the column names are column1, column2, column3 etc, how would I accomplish this?

Answer 1

How about a lateral join that unpivots the columns to rows? This requires enumerating the columns just once, like so:

select t.*, c.cnt
from mytable t
cross apply (
    select count(*) cnt
    from (values (t.column1), (t.column2), (t.column3)) x(col)
    where col is not null
) c
order by c.cnt desc