I'm doing some tests with the new v17 PIVOT clause, based on the according samples in the demo database.
The samples show some methods to specify an IN list with a fixed list of constants, such as
SUM( Salary ) TotalSalary FOR DepartmentID IN ( 100, 200, 300, 400, 500 )
However, how can I use a dynamic list here?
The docs seem to imply that I can only use a constant-expression as the IN list - for all other possibilities, such as "IN variablename", "IN (subquery)", "IN (ALL)", "IN (ANY)", it states
whereas I do want to generate a regular SQL result set and not with one with XML columns.
(Aside: And the docs seem to be precise here, i.e. using "IN (ALL)" leads to a syntax error when used without PIVOT XML:
SELECT * FROM ( SELECT DepartmentID, State, Salary FROM Employees WHERE State IN ( 'OR', 'CA', 'AZ', 'UT' ) ) MyPivotSourceData PIVOT ( SUM( Salary) TotalSalary FOR State IN (ALL) ) PivotedData ORDER BY DepartmentID
returns SQLCODE -131 for "ALL".)
So say, I would want to generate a separate column for each existing DepartmentID and not restrict that to already known values, do I need to use dynamic SQL with EXECUTE IMMEDIATE (with its disadvantages like extra masking of string literals) or is there a better alternative?
Ah, the IN variablename syntax seems to work for non-XML result sets, too (unless otherwise specified in the cited docs, methinks):
Here I'm using an array type and order the array by using ARRAY_AGG with a fitting ORDER BY (and no, I'm not yet using array types regularly...):
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;
In contrast, all attempts with an "In (subquery)" have failed so far.
You can use a variable in the IN clause. This is described in the documentation
For example, here is a block of code that pivots the accumulated property history information (new in V17):
Notice how the list of properties is first stored in the @props variable (as an array) and then that variable is used in the PIVOT clause.
answered 30 Nov '15, 08:47