T-SQL select rows from pivot where all columns are zero
Posted on 2011-09-22
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.