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?

asked 30 Nov '15, 08:18

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659
accept rate: 32%

edited 30 Nov '15, 08:20


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.

permanent link

answered 30 Nov '15, 08:44

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659
accept rate: 32%

edited 30 Nov '15, 08:45

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

permanent link

answered 30 Nov '15, 08:47

Mark%20Culp's gravatar image

Mark Culp
22.9k9129269
accept rate: 41%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×80
×9

question asked: 30 Nov '15, 08:18

question was seen: 417 times

last updated: 30 Nov '15, 08:47