?
Solved

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

Posted on 2011-09-22
9
Medium Priority
?
441 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
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 2000 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

771 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