Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2011-09-22
9
Medium Priority
?
459 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 61

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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 61

Expert Comment

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

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 61

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 61

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

606 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