dot3tech dot3tech - 5 months ago 8
SQL Question

Selecting column names where values are NULL

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>

DVT DVT
Answer

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;
Comments