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:

  ,Field    =  'Length'
 where [Length] is NULL
  ,Field    =  'Colour'
 where [Colour] is NULL

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.

  • Does not look like a duplicate to me. This question is more about selecting the columns which are NULLs and disregarding the columns which are NOT NULL. – umbersar Sep 7 '17 at 0:12

Try this:

WITH CTE(Assessor,Date,Field) AS (
select Assessor,Date,Field
select Assessor,Date,ISNULL(Length,'Length') as Length,ISNULL(Colour,'Colour') as Colour,ISNULL(Weight,'Weight') as Weigth
from TestTable) as OP
Field for Fields IN (Length,Colour,Weigth)) as UP)
select Assessor,Date,Field from CTE
where Field in ('Colour','Length','Weigth')
| improve this answer | |
  • Well i would be careful with using ISNULL as the string could be truncated to the length of the column. – umbersar Sep 7 '17 at 0:27
  • ;WITH INVERT_CTE AS ( SELECT Assessor, Date, CASE WHEN Colour IS NULL THEN 'Colour' END AS Colour, CASE WHEN Weight IS NULL THEN 'Weight' END AS Weight, CASE WHEN Length IS NULL THEN 'Length' END AS Length FROM testtbl ) SELECT Assessor, Date, Field FROM (SELECT Assessor, Date, Length, Colour, Weight FROM INVERT_CTE) AS p UNPIVOT (Field FOR MYFIELD IN (Length, Colour, Weight) )AS unpvt; – umbersar Sep 7 '17 at 0:44
select assessor, DATE, field
from dbo.table1
cross apply (values('length',length),('colour',colour),('weight',weight)) f(field,value)
where value is null

based on https://dba.stackexchange.com/a/117974/106527

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.