Как правильно выполнить транспонирование таблицы? Ошибка: ORA-00936: missing expression

Question

Есть данные в таблице командировок, в которой указано, какая служба, сколько раз и куда съездила. А сам справочник находится в другой таблице и может менятся.

Пытаюсь транспонировать через pivot, но получается не очень:

SELECT * FROM (
  SELECT MESTO_GO, SLUGBA
  FROM TABLE_KOMAND
)
PIVOT
(COUNT (SLUGBA)
 FOR SLUGBA IN (SELECT DISTINCT SLUGBA FROM TABLE_KOMAND)
)
ORDER BY 1;

Выходит ошибка:

ORA-00936ORA-00936: missing expression

А если указать коды служб вручную то всё работает нормально:

FOR SLUGBA IN ('101','102','103')

Как сделать правильно?

Answer 1

Допустим, есть такие данные, и если коды служб известны, то проблем нет:

select * from t1;

CITY CODE        QTY
---- ---- ----------
aaa  101          10
bbb  101          20
aaa  102          30
bbb  102          40
aaa  103          50
bbb  103          60

select * from (
    select city, code, qty
    from t1)
pivot (sum (qty) for code in ('101', '102', '103'));

CITY      '101'      '102'      '103'
---- ---------- ---------- ----------
bbb          20         40         60
aaa          10         30         50

А если неизвестно, какие коды служб существуют, например, происходит вставка, то конечно помечтать можно:

insert into t1 values ('aaa', '104', 40);

select * from (
    select city, code, qty
    from t1)
pivot (sum (qty) for code in (select distinct code from t1))

Но мечте не суждено сбытся:

SQL Error: ORA-00936: missing expression

Дело в том, что колонки, их число и тип, должны быть известны SQL парсеру до того, как запрос будет выполнен, то есть подзапрос с кодами просто не может быть выполнен. Дилемма курицы и яйца - чтобы выполнить надо спарсить, а чтобы спарсить, надо выполнить.

Решение без добавления новых кодов служб вручную: выполнить два запроса, в первом получить колонки, а во второй динамический запрос подставить эти колонки (на db<>fiddle):

var rc refcursor
declare
    cols varchar2 (128);
    stmt varchar2 (32767);
begin
    select listagg (''''||code||''' "'||code||'"', ',') within group (order by code)
    into cols
    from (select distinct code from t1);

    stmt := '
        select * from (
            select city, code, qty
            from t1)
        pivot (sum (qty) for code in ('||cols||'))';
    open :rc for stmt;
end;
/
CITY        101        102        103        104
---- ---------- ---------- ---------- ----------
bbb          20         40         60           
aaa          10         30         50         40

БЛОГ НА HUSL

Как правильно выполнить транспонирование таблицы? Ошибка: ORA-00936: missing expression

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