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. |
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. |
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...
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?
If you want to suppress rows from the result set (i.e. from table expression "tot", simply use a WHERE clause, such as
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...
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).