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

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 .