[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2011-09-22
9
Medium Priority
?
449 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

650 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