Display all fields with null values in a particular row in SQL Server

Question

I have a requirement where I want to fetch and display all the fields with NULL value for a particular row in the table.

Some thing like:

select 'all columns with NULL' 
 from table_xyz 
 where primary_key='pk_row2';

I use SQL Server 2012. Please help achieve this.

• Aaron Bertrand: What do you want to have happen when no column is NULL?

Answer 1

SQL as a language allows you to easily dictate which rows to include in output, but not which columns. For this you'll need dynamic SQL (and a consuming app which is equally dynamic). An example, given this table and data:

USE tempdb;
GO
CREATE TABLE dbo.blat(pk varchar(32) NOT NULL, a int, b int,
 CONSRAINT pk_blat PRIMARY KEY (pk));
INSERT dbo.blat(pk,a,b) VALUES
 ('key 1',1,NULL),
 ('key 2',1,1),
 ('key 3',NULL,1),
 ('key 4',NULL,NULL);

One solution:

-- input param:
DECLARE @pk varchar(32) = 'key 4';
DECLARE @sql nvarchar(max) = N'SELECT pk';
SELECT @sql += 
 CASE WHEN a IS NULL THEN N',a' ELSE N'' END
 + CASE WHEN b IS NULL THEN N',b' ELSE N'' END
FROM dbo.blat WHERE pk = @pk;
SET @sql += N' FROM dbo.blat WHERE pk = @pk;';
EXEC sys.sp_executesql @sql, N'@pk varchar(32)', @pk;

Try it with all four combinations. Don't forget to clean up:

DROP TABLE dbo.blat;