UNPIVOT translates columns into rows. In the process it eliminates NULL values (reference).
Given the input
create table #t
(
ID int primary key,
c1 int null,
c2 int null
);
insert #t(id, c1, c2)
values
(1, 12, 13),
(2, null, 14),
(3, 15, null),
(4, null, null);
the UNPIVOT query
select
ID, ColName, ColValue
from
(
select *
from #t
) as p
unpivot
(
ColValue for ColName in
(c1, c2) -- explicit source column names required
) as unpvt;
will produce the output
| ID | ColName | ColValue |
|----|---------|----------|
| 1 | c1 | 12 |
| 1 | c2 | 13 |
| 2 | c2 | 14 |
| 3 | c1 | 15 |
Sadly row 4 has been eliminated entirely since it has only NULLs! It can be conveniently re-introduced by injecting a dummy value into the source query:
select
ID, ColName, ColValue
from
(
select
-5 as dummy, -- injected here, -5 is arbitrary
*
from #t
) as p
unpivot
(
ColValue for ColName in
(dummy, c1, c2) -- referenced here
) as unpvt;
By aggregating the rows on ID we can count the non-null values. A comparison to the total number of columns in the source table will identify rows containing one or more NULL.
select
ID
from
(
select -5 as dummy, *
from #t
) as p
unpivot
(
ColValue for ColName in
(dummy, c1, c2)
) as unpvt
group by ID
having COUNT(*) <> 3;
I calculate 3 as
number of columns in source table #t
+ 1 for the injected dummy column
- 1 for ID, which is not UNPIVOTED
This value could be obtained at runtime by examining the catalog tables.
The original rows can be retrieved by joining to the results.
If values other than NULL are to be investigated they can be included in a where clause:
...
) as unpvt
where ColValue <> '' -- will eliminate empty strings
Discussion
This requires an identifier that is carried through the UNPIVOT. A key would be best. If none exists one can be injected by the ROW_NUMBER() window function, though this may be expensive to execute.
All columns must be explicitly listed inside the UNPIVOT clause. They can be dragged in using SSMS, as @db2 suggested. It will not be dynamic when the table definition chagnes, as Aaron Bertrand's suggestion would be. This is the case for almost all SQL, however.
For my rather limited data set the execution plan is a clustered index scan and a stream aggregate. This will be more expensive of memory than a straight scan of the table and lots of OR clauses.