好的,4个小时的编码,只有6个小时的搜索…,我没有比我开始时更好.这是我的问题我有一个表(tmp
Shell),它有12列.这是一个没有约束的基本表,用于临时报告.当我们插入数据时,我必须提取一个ID号(PatientId)和所有列NAMES,其中该PatientID的值为null.
例：
PatientIdFnameLnameDOB 123455SamNULLNULL
2345455NULLDoe1/1/1980
09172349JohnJoneNULL
我想回来的是：
PatientIdErrorMsg 123455Lname,DOB
2345455Fname
09172349DOB
当然,如果所有的列都有一个值,那么errormsg将为null.
我尝试并失败了大约300个不同的代码,但这似乎是我能得到的最接近的.不幸的是,这只返回EVERY列,而不是空值.
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('')
),'')
FROM
[tmpShell2] AS [TS];
DROP TABLE [dbo].[tmpShell2];
END;
GO
EXEC [sp_aaShowAllNullColumns]
'tmpShell';
</pre>
解决方法
这样的东西怎么样？
SELECT
a.PatientID,CASE a.tmpCol
WHEN '' THEN NULL
ELSE STUFF(a.tmpCol,'')
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;
