Selecting column names where values are NULL

Question

Ok, 4 hours of coding and only 6 hours of searching... and I'm no better off than when I started. Here's my problem. I have a table (tmpShell) and it has 12 columns. It's a basic table with no constraints - used for temporary reporting. As we insert data, I have to extract an ID number (PatientId) and all column NAMES where the value for that PatientId is null.

Example:

PatientId    Fname    Lname      DOB 
123455       Sam      NULL       NULL
2345455      NULL     Doe        1/1/1980
09172349     John     Jone       NULL

---

What I want to return is:

PatientId    ErrorMsg
123455       Lname,DOB
2345455      Fname
09172349     DOB

Of course, if all columns have a value, the errormsg would be null.

I have tried and failed about 300 different pieces of code, but this appear to be the closest I can get. Unfortunately, this just returns EVERY column, not the nulls.

     ALTER PROC [sp_aaShowAllNullColumns]
      @tableName VARCHAR(255)
     AS
      BEGIN
            SET NOCOUNT ON;


        DECLARE @sql NVARCHAR(4000);
        DECLARE @cols NVARCHAR(4000);
        DECLARE @tcols TABLE ( [colbit] NVARCHAR(255) );
        --DECLARE @tablename VARCHAR(255) = 'tmpShell';
        INSERT @tcols
                SELECT
                        'count(' + [columns].[name] + ') as ' + [columns].[name] + ', ' AS [colbit]
                    FROM
                        [sys].[columns]
                    WHERE
                        [columns].[object_id] = OBJECT_ID(@tableName);


        SELECT
                @cols = COALESCE(@cols, ', ', '') + [@tcols].[colbit]
            FROM
                @tcols;
        SELECT
                @cols = SUBSTRING(@cols, 1, ( LEN(@cols) - 1 ));
        SELECT
                @cols = ISNULL(@cols, '');

        SELECT
                @sql = 'select patientid, count(*) as Rows' + @cols + ' from ' + @tableName + ' group by patientid having count(*) > 0';
        CREATE TABLE [tmpShell2]
               (
                [patientid] VARCHAR(15)
               ,[Rows] CHAR(2)
               ,[Rn] CHAR(2)
               ,[patId] CHAR(2)
               ,[fname] CHAR(2)
               ,[lname] CHAR(2)
               ,[dob] CHAR(2)
               ,[addr1] CHAR(2)
               ,[city] CHAR(2)
               ,[state] CHAR(2)
               ,[zip] CHAR(2)
               ,[country] CHAR(2)
               ,[psite] CHAR(2)
               ,[csite] CHAR(2)
               ,[ssite] CHAR(2)
               ,[scode] CHAR(2)
               ,[sfid] CHAR(2)
               ,[taskid] CHAR(2)
               ,[errormsg] CHAR(2)
               );
        INSERT INTO [tmpShell2]
                EXEC [sys].[sp_executesql]
                    @sql;

        DECLARE @tbl VARCHAR(255) = 'tmpShell2';
        SELECT DISTINCT
                [TS].[patientid]
            ,   STUFF((
                        SELECT DISTINCT
                                ', ' + [C].[name]
                            FROM
                                [tmpShell2] AS [TS2]
                                JOIN [sys].[columns] AS [C]
                                ON [C].[object_id] = OBJECT_ID(@tbl)
                            WHERE
                      [C].[name] NOT IN ( 'SFID', 'TaskId',     'ErrorMsg' )
                                AND [C].[name] IS NOT NULL
                      FOR
                        XML PATH('')
                      ), 1, 1, '')
            FROM
                [tmpShell2] AS [TS];

        DROP TABLE [dbo].[tmpShell2];

  END;
 GO
 EXEC [sp_aaShowAllNullColumns]
'tmpShell';
</pre>

Answer 1

This is a stored procedure that will take in the schema name, table name and column name, then produce the desired result.

ALTER PROCEDURE [dbo].[DynamicErrorProducing]
@schema_name NVARCHAR(100),
@table_name  NVARCHAR(100),
@column_name NVARCHAR(100)
AS
BEGIN
DECLARE @countTable  TINYINT;
DECLARE @countColumn TINYINT;
DECLARE @string NVARCHAR(MAX);
DECLARE @sqlString NVARCHAR(MAX);

SET @countTable=0;

SELECT
    @countTable=COUNT(*)
FROM
    sys.tables
WHERE
    SCHEMA_NAME(schema_id)=@schema_name
    AND name=@table_name;

-- If there is no table as described, quit with an error
IF (@countTable = 0)
    RETURN 0;

SET @countColumn=0;

SELECT
    @countColumn=COUNT(*)
FROM
    sys.columns
WHERE
    object_id = OBJECT_ID(@schema_name+'.'+@table_name)
    AND name=@column_name;

IF (@countColumn<> 1)
    RETURN;

SELECT
    @countColumn=COUNT(*)
FROM
    sys.columns
WHERE
    object_id = OBJECT_ID(@schema_name+'.'+@table_name)
    AND name<>@column_name;

IF (@countColumn<1)
    RETURN;

SELECT @string=STUFF(
    (
    SELECT
        '+ CASE WHEN '+name+' IS NULL THEN '''' ELSE '','+name+''' END '
    FROM
        sys.columns
    WHERE
        object_id = OBJECT_ID(@schema_name+'.'+@table_name)
        AND name<>@column_name
    FOR XML PATH(''))
    ,1,2,'');


SET @sqlString=N'
SELECT
    a.'+@column_name+'
    , CASE WHEN a.tmpCol='''' THEN NULL ELSE STUFF(a.tmpCol,1,1,'''') END AS ErrMsg
FROM
(
SELECT
    '+@column_name+'
    ,'+@string+' AS tmpCol
FROM
    '+@schema_name+'.'+@table_name+'
) a';

EXEC sp_executesql
    @statement=@sqlString;

END


GO

Answer 2

Here's a SQLfiddle based on https://stackoverflow.com/a/38036046/2314737 with handling of the commas

http://sqlfiddle.com/#!9/708796/1

SELECT PatientId,
    REPLACE(RTRIM(CONCAT(CASE WHEN Fname IS NULL THEN 'Fname ' ELSE '' END,
           CASE WHEN Lname IS NULL THEN 'Lname ' ELSE '' END,
           CASE WHEN DOB IS NULL THEN 'DOB' ELSE '' END)),
            ' ',',')
FROM YourTable

Answer 3

How about something like this?

SELECT
a.PatientID
, CASE a.tmpCol
    WHEN '' THEN NULL
    ELSE STUFF(a.tmpCol,1,1,'')
END AS ErrorMsg
FROM
(
SELECT
    PatientID
    , CASE WHEN FirstName IS NULL THEN ',FirstName' ELSE '' END
    + CASE WHEN LastName IS NULL THEN ',LastName' ELSE '' END
    + CASE WHEN DOB IS NULL THEN ',DOB' ELSE '' END AS tmpCol
FROM
    <tableName>
) a;

Answer 4

I think you over complicated things.

You can try using CASE EXPRESSION :

SELECT t.patientID,
       CASE WHEN t.fname is NULL THEN 'Fname,' ELSE '' END +
       CASE WHEN t.Lname is NULL THEN 'Lname,' ELSE '' END +
       CASE WHEN t.DOB is NULL THEN 'DOB,' ELSE '' END 
       ..... as ErrorMsg
FROM YourTable t

This will result with an unnecessary comma in the end of the errorMsg , to handle it you can do this:

       REPLACE(CASE... +
               CASE... +
               CASE WHEN t.DOB is NULL THEN 'DOB,' ELSE '' END 
               ..... + ' ') ', ','') as ErrorMsg

This will make the last comma unique because it will have a space concatenate to it and will make sure only it will be deleted .

Answer 5

You can use a pivot query to get the result you want. The following query should approximate your desired output:

SELECT PatientId,
    CONCAT(CASE WHEN Fname IS NULL THEN 'Fname ' ELSE '' END,
           CASE WHEN Lname IS NULL THEN 'Lname ' ELSE '' END,
           CASE WHEN DOB IS NULL THEN 'DOB' ELSE '' END)
FROM yourTable