SQL Server – Selecting Columns Where SUM(Column) > 0

sql serversql server 2014

I have a table like this:

Date     Bob     Joe     Will
1/1/15    1       0        1
1/2/15    0       0        1
1/3/15    0       0        0
1/4/15    1       1        1

Preferred solution

With one statement, I want to return only the columns where SUM(Column) > 0

For example:

SELECT SUM(Bob) as Robert, SUM(Joe) as Joseph, SUM(Will) as William
  FROM table
 WHERE Date BETWEEN '1/1/15' and '1/3/15'

I want my result to look like this:

Robert     William
  1           2

In other words, since SUM(Joe) = 0 for my selected date range, I don't want the column Joseph to appear in the result set. What is the magic that would cause Joseph to not appear?

Alternate Solution

I can work with the data if it comes back slightly differently, so I have considered writing a table function to return a 2-column table like this:

Name        Value
Robert      1
William     2

My thought is that the function code would look similar to this:

--Declare table here
IF SELECT SUM(Bob) > 0
  INSERT INTO temp (Name, Value)
    ('Robert', SELECT SUM(Bob))

Lather, rinse, repeat for each of my columns. I know this isn't ideal since it would need maintenance should there ever be a new column that needs to considered, but there's other maintenance in that situation anyway, so I could handle it.

In Summary

Is there a way to create a query that would give me my preferred solution? If not, are my thoughts on my alternate solution reasonable?

Best Answer

This solution gives you your "Alternate Solution" answer. The only way to achieve what you are looking for as a primary solution would be a dynamic pivot operation on the result set after you have unpivoted it. This solution does require maintenance but is straightforward and no function is required.

Sample data:

DECLARE @Names TABLE
    (
      DateValue DATE NOT NULL
    , Bob INT NOT NULL
    , Joe INT NOT NULL
    , Will INT NOT NULL
    );

DECLARE @MinDate DATE = '1/1/2015';
DECLARE @MaxDate DATE = '1/3/2015';
DECLARE @MinValue INT = 1;

INSERT  INTO @Names
        ( DateValue, Bob, Joe, Will )
VALUES  ( '1/1/2015', 1, 0, 1 )
        , ( '1/2/2015', 0, 0, 1 )
        , ( '1/3/2015', 0, 0, 0 )
        , ( '1/4/2015', 1, 1, 1 );

Query: 

WITH    CTE_Unpivot
          AS ( SELECT   DateValue
                      , ScoreName
                      , Scores
               FROM     ( SELECT    DateValue
                                    , Bob
                                    , Joe
                                    , Will
                          FROM      @Names
                        ) AS cp 
               UNPIVOT  ( Scores FOR ScoreName IN ( Bob, Joe, Will ) ) AS up
             )
    SELECT  ScoreName
          , SUM(Scores) AS ScoreTotal
    FROM    CTE_Unpivot
    WHERE   DateValue BETWEEN @MinDate AND @MaxDate
    GROUP BY ScoreName
    HAVING  SUM(Scores) >= @MinValue;

Edit:
Ideally, you wouldn't have the table stored like that, it looks like it was copied from an Excel spreadsheet. You would have a table with three columns, date, name and value, then query based on that.

Related Question