Как задать столбцы для функции PIVOT динамически подзапросом?

Question

В этом вопросе стояла задача: развернуть строки в столбцы за 12 месяцев, другими словами кол-во и имена столбцов были заранее известны.

А что если желаемые столбцы в результате заранее неизвестны?

Функция PIVOT допускает использовать подзапрос (или даже ключевое слово ANY) (см. pivot_in_clause), но только в сочетании с ключевым словом XML для вывода в XML формате:

The optional XML keyword generates XML output for the query. The XML keyword permits the pivot_in_clause to contain either a subquery or the wildcard keyword ANY. Subqueries and ANY wildcards are useful when the pivot_in_clause values are not known in advance.

Например, запрос и его результат за весь период будут выглядеть так:

select * from (
    select name, qty, to_char (dt, 'mon') mon
    from tstdata)
pivot xml (sum (qty) for mon in (select distinct to_char (dt, 'monyy') mon from tstdata))
/
NAME     MON_XML                                                                         
-------- --------------------------------------------------------------------------------
Петя     <PivotSet><item><column name = "MON">apr19</column><column name = "SUM(QTY)"></c
Саша     <PivotSet><item><column name = "MON">apr19</column><column name = "SUM(QTY)"></c

А как получить результат сразу в табличном виде и с чем связано это ограничение?

Можно конечно распарсить XML, например, написав PL/SQL табличную функцию, но это ведь не так просто.

Генерация тестовых данных db<>fiddle (см. историю правок, если ссылка недоступна).

Answer 1

Столбцы в функции PIVOT, их кол-во и тип данных, на момент парсинга (hard-parse) должны быть известены. XML результат, это один столбец и тип его известен, отсюда и ограничение.

Самый простое решение, сделать два запроса, первым получить желаемые столбцы, а вторым получить результат для этих столбцов. Как-то так:

set autoprint on
var rc refcursor
begin
    for r in (
        select listagg (''''||mon||''' '||mon, ',') within group (order by mm) cols 
        from (
            select distinct to_char (dt, 'monyy') mon, to_char (dt, 'yyyymm') mm from tstdata)
        ) loop 
        open :rc for '
            select * from (
                select name, qty, to_char (dt, ''monyy'') mon
                from tstdata)
            pivot (sum (qty) for mon in ('||r.cols||'))';
    end loop;
    if not :rc%isopen then open :rc for select 'no data found' msg from dual; end if; 
end;
/

Результат (укорочен для наглядности):

NAME          JAN19      FEB19      MAR19      APR19 [...]      NOV20      DEC20
-------- ---------- ---------- ---------- ----------       ---------- ----------
Петя             32         28         30         30 [...]         30         30
Саша             15         14         16         15 [...]         15         15

Answer 2

Существуют также другие решения без функции PIVOT, которые пытаются определить столбцы в сете результата динамическии. Как в частности:

Т.н. Antons pivoting любезно предоставленный участником @MaxU
Dynamic Pivot PFT пример реализации опубликованный участником @Chris Saxon

Они конечно дают некоторый комфорт по сравнению с решением с двумя запросами.
Но сновной принцип остаётся прежним: столбцы в сете результата должны быть известны парсеру SQL; после компиляции запроса, нет никакой возможности изменить столбцы в сете результата.

Посмотрим на примере Dynamic Pivot with PFT, т.к. код на Live SQL проще воспроизвести:

with rep as (
    select name, to_char (dt, 'yyyy') year  
    from tstdata where name='Саша'
)
select * 
from dynamic_pivot_ptf (
    rep, columns (year), 
    'select distinct to_char (dt, ''yyyy'') from tstdata')
/

NAME           2019       2020
-------- ---------- ----------
Саша            182        183

delete from tstdata where name='Саша' and dt<date'2020-01-01'; 

182 rows deleted.

После удаления вроде ожидается, что и столбец 2019 теперь тоже "динамически" исчезнет.
Нет, это не так. Повторите запрос выше. Он не изменился, его скомпилированный байт-код будет вызван из Library Cache, соответственно и столбцы в сете результата останутся неизменными:

NAME           2019       2020
-------- ---------- ----------
Саша           null        183

БЛОГ НА HUSL

Как задать столбцы для функции PIVOT динамически подзапросом?

Ответы (2 шт):