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