wlevy
asked on
T-SQL select rows from pivot where all columns are zero
I have a stored procedure in SQL Server 2008 for a report. The SPROC uses a dynamic pivot to generate a rather wide result set, anywhere from a dozen to a hundred columns. Now I have a requirement to optionally suppress rows where all of the aggregate values (i.e., the dynamically created columns) are zero.
RowHeader Col_1 Col_2 ... Col_95
abc 1 0 14
xyz 0 0 0
In the above example, row "xyz" should not be included in the result set. I can build a long WHERE clause (WHERE Col_1 <> 0 AND Col_2 <> 0 ... AND Col_95 <> 0) but I'm looking for a better solution. Rows can contain a mix of positive and negative values so I can't just sum them.
I will accept an answer of "There is no better way to do this" if corroborated by two experts.
Thanks
RowHeader Col_1 Col_2 ... Col_95
abc 1 0 14
xyz 0 0 0
In the above example, row "xyz" should not be included in the result set. I can build a long WHERE clause (WHERE Col_1 <> 0 AND Col_2 <> 0 ... AND Col_95 <> 0) but I'm looking for a better solution. Rows can contain a mix of positive and negative values so I can't just sum them.
I will accept an answer of "There is no better way to do this" if corroborated by two experts.
Thanks
If you have control over the original stored procedure, you can filter the data source to the pivot to exclude those rows where the SUM() = 0. You can do this in a number of ways, including using SUM() OVER() in a derived table and then filtering on the outer query for SUM > 0. You can do a separate derived table that aggregates based on RowHeader and you can just add a HAVING clause that filters SUM > 0. Then by way of INNER JOIN to this derived table, the other one should reflect only data that has at least one nonzero value.
ASKER
mwvisa1 - Values can be positive or negative, so if for a given RowHeader I have values of 5 and -5 they would sum to zero but that row should not be excluded.
Okay, then use MAX(). If MAX() is 0, then exclude it. Does that make sense?
Additionally, you can account for NULL by using MAX(COALESCE(col, 0)) << check if 0 >> or MAX(NULLIF(col, 0)) << check if NULL >>.
ASKER
MAX() won't work either. Consider the case where one or more values for a RowHeader is less than zero.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
And by the way, you can use count(nullif(value, 0)) also. That is what I was originally thinking with max as max(nullif(value, 0)) will yield the non-null values even if negative. So max between NULL and -1 is -1. The only way the end result would be NULL is if all the values are NULL or 0. So max would have worked also.
ASKER
Right on the money, mwvisa1. Thank you!
Greg