CODEWITHSUNDEEP
oracle-databaseoracle11g
craig
craig

Reputation: 26262

Use a sub-select in the PIVOT's FOR clause?

The standard PIVOT syntax uses a static FOR list:

SELECT  *
FROM    (
  SELECT log_id, event_id, event_time
  FROM   patient_events
  WHERE  event_id IN (10,20,30,40,50)
) v
PIVOT (
  max(event_time) event_time
  FOR event_id IN( 10,20,30,40,50 )
)

Is there a way to make this dynamic?

I know the sub-select in the WHERE clause will work, but can I use one in the FOR?

SELECT  *
FROM    (
  SELECT log_id, event_id, event_time
  FROM   patient_events
  WHERE  event_id IN ( sub-select to generate list of IDs )
) v
PIVOT (
  max(event_time) event_time
  FOR event_id IN( sub-select to generate list of IDs )
)

Upvotes: 1

Views: 661

Answers (2)

Pablissimo
Pablissimo

Reputation: 2905

You can't in pure SQL, but I don't think quite because of the reason suggested - it's not that the IN clause needs to be ordered, it's that it has to be constant.

When given a query, the database needs to know the shape of the result set and the shape needs to be consistent across queries (assuming no other DDL operations have taken place that might affect it). For a PIVOT query, the shape of the result is defined by the IN clause - each entry becomes a column, with a data type corresponding to the aggregation clause.

Hypothetically if you were to allow a sub-select for the IN clause then you could alter the shape of the result set just by performing DML operations. Imagine your sub-select worked and got you a list of all event_ids known to the system - by inserting a new record into whatever drives that sub-select, your query returns a different number of columns even though no DDL has occurred.

Now we're stuck - any view built on that query is invalid because its shape wouldn't match that of the query, but Oracle couldn't know that it's invalid because none of the objects it depends on have been changed by DDL.

Depending on where you're consuming the result, dynamic SQL's your only option - either at the application level (build the IN list yourself) or via a ref cursor in a database function or procedure.

Upvotes: 2

user5683823
user5683823

Reputation:

Interesting question.

On the face of it, it shouldn't work, since the list of values (which will become column names) must be ordered. This is not the case for an "IN" list in the WHERE clause. But perhaps it would work with an ORDER BY condition in the sub-SELECT?

Unfortunately, no. This is easy to test. Got the same error message with or without ORDER BY. (And the query works fine if the IN list is just 10, 20, 30, 40 - the actual department numbers from the DEPT table.) Using tables from the standard SCOTT schema.

SQL> select deptno from scott.dept;

    DEPTNO
----------
        10
        20
        30
        40
4 rows selected.

SQL> select * from (
  2    select sal, deptno
  3    from scott.emp
  4  )
  5  pivot (sum(sal) as total_sal
  6         for deptno in (10, 20, 30, 40))
  7  ;

10_TOTAL_SAL 20_TOTAL_SAL 30_TOTAL_SAL 40_TOTAL_SAL
------------ ------------ ------------ ------------
        8750        10875         9400

1 row selected.

SQL> select * from (
  2    select sal, deptno
  3    from scott.emp
  4  )
  5  pivot (sum(sal) as total_sal
  6         for deptno in (select deptno from scott.dept order by deptno))
  7  ;
       for deptno in (select deptno from scott.dept order by deptno))
                      *
ERROR at line 6:
ORA-00936: missing expression

Upvotes: 1

Related Questions