I have the following query.

    --CREATE VARIABLE @varDepartments INT ARRAY;
SET @varDepartments =
   (SELECT ARRAY_AGG(DISTINCT siteno ORDER BY siteno) FROM ptrans 
        join activitycode ac
            on ac.activitycode = ptrans.activitycode
            and ac.rcode = @currentrcode 
            and ac.activitycodetype = 4
    where siteno is not null  );

select * from (select s.sname,siteno, isnull(c.cntrname,'No Contractor') as cntrname, transtype,payamount from ptrans join activitycode ac on ac.activitycode = ptrans.activitycode and ac.rcode = @currentrcode left outer join contractor c on c.recno = ptrans.cntrid left outer join sale s on s.recno = ptrans.saleid where ac.activitycodetype = 4 and ptrans.delflag = 0 ) as ps pivot ( sum(case when transtype='A' then payamount*-1 else payamount end) holdbacksite for siteno in @varDepartments ) as tot order by cntrname,sname

This works fine but I need to be able to suppress all rows that have a total of 0 in all the columns. Is that possible? Thanks.

asked 09 Mar '16, 13:45

jimboidaho_gplus's gravatar image

jimboidaho_g...
1463616
accept rate: 0%

Just to understand: What rows do you refer to - rows from the underlying tables like "ptrans" or from the result set? The latter can be filter as usual with a simple WHERE clause...

(10 Mar '16, 06:12) Volker Barth

The result set. The "for siteno in @varDepartments" is dynamic so I am not sure how I would add a where when the column count can be different?

(10 Mar '16, 10:29) jimboidaho_g...
Replies hidden

If you want to suppress rows from the result set (i.e. from table expression "tot", simply use a WHERE clause, such as

...ldbacksite
     for siteno in @varDepartments   ) as tot
where <whatever>

Or do you intend to remove colums from the result set, not rows?


FWIW, it would be helpful to show a possible result set (and the rows/columns that should be suppressed), or to give us a reproducible...

(10 Mar '16, 10:54) Volker Barth

I want to filter rows that are 0. There can be two to four rows based on the location the query is run. So where 01_holdbacksite+02_holdbacksite+03_holdbacksite > 0. The problem is the first two characters can be different (01 - 99).

(10 Mar '16, 11:18) jimboidaho_g...

OK, I guess I got the point - as stated, you can use a WHERE clause to filter within the result set - but apparently that's not that easy if you want to filter based on the pivot columns when these are built dynamically and so their names are unknown beforehand.

I think one way to overcome that is to add an aggregate over all those columns that you would require to filter and join that with the pivot derived table.

Here's a sample pivot query taken from that other question, originally based on a sample from the v17 docs and the v17 demo database:

CREATE VARIABLE @varDepartments INT ARRAY;
SET @varDepartments =
   (SELECT ARRAY_AGG(DISTINCT DepartmentID ORDER BY DepartmentID) FROM Employees);
SELECT * 
FROM ( SELECT DepartmentID, State, Salary 
       FROM   Employees
       WHERE State IN ( 'OR', 'CA', 'AZ', 'UT' )
     ) MyPivotSourceData
   PIVOT ( 
      SUM( Salary ) TotalSalary  
      FOR DepartmentID IN @varDepartments
   ) MyPivotedData
ORDER BY State;

This returns the following from the demo database:

alt text


Now, say I want to filter on the "summed total" of the different "department-specific totals". Not aware how they will be named (department IDs may come and go, so next time I could have a "600_TotalSalary" column in the result set), I can not simply build a WHERE clause for unknown columns.

But I could add a derived table that calculates the total sum per state, and would join that with the above query. Then I can apply a filter condition on that added column easily because its name is specified explicitly - in the sample, the filter condition would not suppress a row but could be easily modified to do so:

SELECT MyPivotedData.*, MySumPerState.TotalSalary 
FROM ( SELECT DepartmentID, State, Salary 
       FROM   Employees
       WHERE State IN ( 'OR', 'CA', 'AZ', 'UT' )
     ) MyPivotSourceData
   PIVOT ( 
      SUM( Salary ) TotalSalary  
      FOR DepartmentID IN @varDepartments
   ) MyPivotedData
   INNER JOIN
   ( SELECT State, Sum(Salary) as TotalSalary
       FROM   Employees
       WHERE State IN ( 'OR', 'CA', 'AZ', 'UT' )
       GROUP BY State
     ) MySumPerState ON MyPivotedData.State = MySumPerState.State
WHERE TotalSalary > 150000 -- Added filter
ORDER BY MyPivotedData.State;

returns

alt text

I guess you could use a similar aggregate over all your pivot columns to filter on your requirements, possibly here for those with SUM over all columns = 0.

permanent link

answered 10 Mar '16, 17:31

Volker%20Barth's gravatar image

Volker Barth
29.6k293444650
accept rate: 32%

edited 10 Mar '16, 17:34

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×8

question asked: 09 Mar '16, 13:45

question was seen: 329 times

last updated: 10 Mar '16, 17:34