5

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.

SynapseHub
  • 51
  • 1

2 Answers2

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')
S4V1N
  • 1,084
  • 6
  • 12
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

Daniel Jelinski
  • 83
  • 1
  • 9