例：

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>