CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
evanburen
evanburen

Reputation: 309

Exclude NULL Columns in Results

My query below returns these results

enter image description here

SELECT 
     BorrowerNumber
     ,CASE WHEN [Score_CorrectID_Accuracy] = 'Error' THEN 'Error' END AS [Correct ID]
     ,CASE WHEN [Score_ProperlyIdentified_Accuracy] = 'Error' THEN 'Error' END AS [PCA Identified Itself]
     ,CASE [Score_Tone_Accuracy] WHEN 'Error' THEN 'Error' END AS [Score_Tone_Accuracy]
     ,CASE [Score_Accuracy_Accuracy] WHEN 'Error' THEN 'Error' END AS [Score_Accuracy_Accuracy]
     ,CASE [Score_Notepad_Accuracy] WHEN 'Error' THEN 'Error' END AS [Score_Notepad_Accuracy] 
    FROM 
        Reviews INNER JOIN QCTier1 ON Reviews.ReviewID = QCTier1.ReviewID

These are the results I'm looking to achieve where the last column is not in the results because it has a NULL value. Thanks

Desired results

Upvotes: 0

Views: 7857

Answers (3)

Eric
Eric

Reputation: 703

You won't be able to exclude the columns themselves from the result set without using some really wacky and difficult code. But what you can do (in pretty much every reporting tool) is show or hide columns in your report based on a formula or even a single value.

So take your existing query, and for each column that you may want to hide, add another column that aggregates your "hide column" over the whole dataset.

SELECT 
    sq.*,
    max(sq.[Correct ID]) as MAX_CORRECT_ID,
    max(sq.[PCA Identified Itself]) as MAX_PCA_ID_SELF
    -- ... rinse and repeat
FROM (
    SELECT 
         BorrowerNumber
         ,CASE WHEN [Score_CorrectID_Accuracy] = 'Error' THEN 'Error' END AS [Correct ID]
         ,CASE WHEN [Score_ProperlyIdentified_Accuracy] = 'Error' THEN 'Error' END AS [PCA Identified Itself]
         ,CASE [Score_Tone_Accuracy] WHEN 'Error' THEN 'Error' END AS [Score_Tone_Accuracy]
         ,CASE [Score_Accuracy_Accuracy] WHEN 'Error' THEN 'Error' END AS [Score_Accuracy_Accuracy]
         ,CASE [Score_Notepad_Accuracy] WHEN 'Error' THEN 'Error' END AS [Score_Notepad_Accuracy] 
        FROM 
            Reviews INNER JOIN QCTier1 ON Reviews.ReviewID = QCTier1.ReviewID
) sq

Anytime the MAX column is null in the first row, you can be sure there were no non-null values in the corresponding column.

In my opinion for a result set that typically returns only a few rows, this is a monumental waste of time since you could just use a simple aggregation or formula in your reporting tool to conditionally hide the columns, and keep your SQL clean. If you had hundreds of thousands of rows in the data set, or if you had more complex requirements requiring grouping or windowing or both, then this could possibly be worthwhile. For example, sometimes it's useful to show aggregate data for all rows sharing a specific attribute with the current row, without grouping the whole report by that column.

Upvotes: 1

John Wu
John Wu

Reputation: 52280

Try this:

DECLARE @COUNT int

SELECT @COUNT = COUNT(*)
FROM Reviews INNER JOIN QCTier1 ON Reviews.ReviewID = QCTier1.ReviewID
WHERE Score_Notepad_Accuracy IS NOT NULL

IF @COUNT = 0 THEN
    SELECT 
       BorrowerNumber
      ,CASE WHEN [Score_CorrectID_Accuracy] = 'Error' THEN 'Error' END AS [Correct ID]
      ,CASE WHEN [Score_ProperlyIdentified_Accuracy] = 'Error' THEN 'Error' END AS [PCA Identified Itself]
      ,CASE [Score_Tone_Accuracy] WHEN 'Error' THEN 'Error' END AS [Score_Tone_Accuracy]
      ,CASE [Score_Accuracy_Accuracy] WHEN 'Error' THEN 'Error' END AS [Score_Accuracy_Accuracy]
    FROM 
       Reviews INNER JOIN QCTier1 ON Reviews.ReviewID = QCTier1.ReviewID
ELSE
    SELECT 
       BorrowerNumber
      ,CASE WHEN [Score_CorrectID_Accuracy] = 'Error' THEN 'Error' END AS [Correct ID]
      ,CASE WHEN [Score_ProperlyIdentified_Accuracy] = 'Error' THEN 'Error' END AS [PCA Identified Itself]
      ,CASE [Score_Tone_Accuracy] WHEN 'Error' THEN 'Error' END AS [Score_Tone_Accuracy]
      ,CASE [Score_Accuracy_Accuracy] WHEN 'Error' THEN 'Error' END AS [Score_Accuracy_Accuracy]
    FROM 
       Reviews INNER JOIN QCTier1 ON Reviews.ReviewID = QCTier1.ReviewID

Personally I would never do it this way. You should hide the column in the reporting program, not in the data source.

Upvotes: 1

Hawtinjr
Hawtinjr

Reputation: 49

As far as I know standard tsql is not able to display/hide a column based on value.

You can implement this in your further programs/UI , if you are programming.

There is another interesting approach to solve this with iff and pivoting here in stackoverflow, but thats way too complicated for this business need!

Maybe you should try to implement this feature in your report program, not in SQL.

Upvotes: 1

Related Questions