How can I specify the IN list for a PIVOT clause dynamically?

[VolkerBarth]

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

"Specify the XML keyword when using this form of the IN clause."

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?

[VolkerBarth · Accepted solution]

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.

[MarkCulp]

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

begin
  create or replace variable @props varchar(120) array;
  select *
    into #propdata
    from sp_property_history();
  set @props = ( select array_agg( distinct name order by name ) from #propdata );
  select *
    from ( select name, ticks, time_recorded, time_delta, value_delta from #propdata ) mysourcedata
   pivot ( sum( value_delta ) delta for name in @props ) mydata
   order by ticks desc;
end;

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.

HTH