Solved

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

Posted on 2011-09-22
9
436 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 60

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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36583356
Okay, then use MAX(). If MAX() is 0, then exclude it. Does that make sense?
0
 
LVL 60

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 60

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 60

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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

717 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