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>

Do you have to do this on unknown number of tables or just this one? — IVNSTN
– IVNSTN, Commented Jun 26, 2016 at 7:08
What is the likelihood that the columns (other than the key) in this table will change, or that there will be columns where you don't care if there is a null? — Xavon_Wrentaile
– Xavon_Wrentaile, Commented Jun 26, 2016 at 15:04
Yes, the number of tables is unknown and the columns will vary from table to table.The likelyhood anything will change is very slim — RazorSharp
– RazorSharp, Commented Jun 26, 2016 at 19:20

sagi · Accepted Answer · 2016-06-26 07:13:08Z

8

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 .

edited Jun 26, 2016 at 7:13

answered Jun 26, 2016 at 7:08

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Biegeleisen Over a year ago

How do you plan to handle the case where only Fname appears for a given record? Then it will have a dangling comma.

DVT · Accepted Answer · 2016-06-26 07:26:36Z

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;

answered Jun 26, 2016 at 7:26

DVT

3,1274 gold badges16 silver badges25 bronze badges

2 Comments

RazorSharp Over a year ago

I'm using this for now, but I would love a more dynamic way that is flexible to various tables and column names.

DVT Over a year ago

I added an answer below that I believe is flexible to tables and column names.

Tim Biegeleisen · Accepted Answer · 2016-06-26 07:10:35Z

2

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

answered Jun 26, 2016 at 7:10

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

1 Comment

sagi Over a year ago

He wanted the list with commas , 14 columns with out a comma won't be readable.

Community · Accepted Answer · 2017-05-23 11:44:15Z

1

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

edited May 23, 2017 at 11:44

CommunityBot

11 silver badge

answered Jun 26, 2016 at 7:32

user2314737

29.7k20 gold badges109 silver badges126 bronze badges

3 Comments

user2314737 Over a year ago

hi @sagi thanks for your feedback. When all fields are not NULL there will be an empty space, I guess one needs to do another select on this select to exclude rows with empty spaces.

user2314737 Over a year ago

@sagi doesn't the RTRIM before the REPLACE take care of that?

sagi Over a year ago

Yes, I missed that sorry.

DVT · Accepted Answer · 2016-07-03 16:28:39Z

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

Collectives™ on Stack Overflow

Selecting column names where values are NULL

5 Answers 5

1 Comment

2 Comments

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

2 Comments

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related