Solved

T-SQL select rows from pivot where all columns are zero

Posted on 2011-09-22
9
394 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:wlevy
  • 5
  • 3
9 Comments
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36582104
Since you are dynamically creating the columns, I would imagine that you have to dynamically build your where clause too.  Ultimately you have to interrogate each column after the aggregation to determine if they are all zero.  I don't think there is any shortcut around that.

Greg

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36582187
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.
0
 

Author Comment

by:wlevy
ID: 36582243
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.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36583356
Okay, then use MAX(). If MAX() is 0, then exclude it. Does that make sense?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36583362
Additionally, you can account for NULL by using MAX(COALESCE(col, 0)) << check if 0 >> or MAX(NULLIF(col, 0)) << check if NULL >>.
0
 

Author Comment

by:wlevy
ID: 36583558
MAX() won't work either. Consider the case where one or more values for a RowHeader is less than zero.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36583815
Sorry. You are right. I was in a rush. Here, use this:
;with 
/* test data only, remove this part */
your_table(RowHeader, Col, Value) as (
   select 'abc', 'Col_1', 1 union all
   select 'abc', 'Col_2', 0 union all
   select 'abc', 'Col_95', 14 union all
   select 'xyz', 'Col_1', 0 union all
   select 'xyz', 'Col_2', 0 union all
   select 'xyz', 'Col_95', 0 union all
   select '123', 'Col_1', -1 union all
   select '123', 'Col_2', 1 union all
   select '123', 'Col_95', 0 
), /* end of test data */
cte as (
select RowHeader, Col, Value
     , count(case when Value <> 0 then Col end) 
	      over(partition by RowHeader) cnt
from your_table
) 
select p.*
from (select RowHeader, Col, Value from cte where cnt > 0) t
pivot (sum(value) for col in (Col_1, Col_2, /*...,*/ Col_95)) p
;

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36583834
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.
0
 

Author Closing Comment

by:wlevy
ID: 36584590
Right on the money, mwvisa1. Thank you!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
combining condition to rerun if failure within BEGIN..END 27 87
Scheduling Jobs for Execution: 4 22
Convert SP in a format for debugging 7 30
Merge Statement 3 40
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now