I manage an application that has multiple users entering data through a web front-end into an MSSQL database. Each individual 'record' can have around 100 database columns across multiple tables (sometime multiple rows in the same table). It is relatively trivial to write SQL queries that will output 1 row per 'record' with all the columns we require for reporting purposes, for example:
Assessor Date Length Colour Weight Steve 2/4/17 23.4 NULL 45 John 4/4/17 NULL Blue NULL Brenda 4/4/17 NULL NULL NULL
I want to produce a simple output that lists everything where data was not recorded i.e fields that remain NULL. For example:
Assessor Date Field Steve 2/4/17 Colour John 4/4/17 Length John 4/4/17 Weight Brenda 4/4/17 Length Brenda 4/4/17 Length Brenda 4/4/17 Colour Brenda 4/4/17 Weight
Currently I've tried something along these lines:
select assessor ,date ,Field = 'Length' from dbo.table1 where [Length] is NULL UNION ALL select assessor ,date ,Field = 'Colour' from dbo.table1 where [Colour] is NULL UNION ALL ...
Is there any way to produce my required output without building such a long list of unions? I've looked into UNPIVOT however this appears to exclude NULLs.