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