suppress null values pivot()

[Former Member]

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.

[VolkerBarth]

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

[Former Member]

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?

[VolkerBarth]

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

[Former Member]

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).

[VolkerBarth]

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:

---

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

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.