5

This question already has an answer here:

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.

marked as duplicate by Marco, Erik Darling, RDFozz, SqlWorldWide, hot2use Sep 6 '17 at 18:59

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 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
3

Try this:

WITH CTE(Assessor,Date,Field) AS (
select Assessor,Date,Field
from(
select Assessor,Date,ISNULL(Length,'Length') as Length,ISNULL(Colour,'Colour') as Colour,ISNULL(Weight,'Weight') as Weigth
from TestTable) as OP
unpivot(
Field for Fields IN (Length,Colour,Weigth)) as UP)
select Assessor,Date,Field from CTE
where Field in ('Colour','Length','Weigth')
  • 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
0
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

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