How can I use pivot to find the records with the most columns populated?

November 26, 2021

I have a problem where I have 5 columns.

What I want to do is add a count on the end with the number of columns where there is no null value.

I am trying to use pivot as this seems to be the most logical SQL clause. Any ideas on this? I haven’t used Pivot in many instances so this is new for me.

>Solution :

An inline pivot/conditional aggregate and a COUNT seems to be what you want here. As all your columns have different data types, you need to also use some CASE expressions. Something like this:

SELECT ID,
       a,
       ...
       (SELECT COUNT(V.C)
        FROM (VALUES(CASE WHEN a IS NOT NULL THEN 1 END),
                    (CASE WHEN b IS NOT NULL THEN 1 END),
                    (CASE WHEN c IS NOT NULL THEN 1 END),
                    (CASE WHEN d IS NOT NULL THEN 1 END),
                    (CASE WHEN e IS NOT NULL THEN 1 END),
                    (CASE WHEN f IS NOT NULL THEN 1 END))V(C)) AS NonNullColumns
FROM dbo.YourTable;