UNPIVOT

📖 Описание

Оператор Oracle SQL UNPIVOT преобразует столбцы в строки. Это полезно при подготовке данных для отчётов, сравнении показателей и преобразовании широких таблиц в длинный формат.

Основные применения:

  1. Преобразование отдельных столбцов периодов в пары «период — значение».
  2. Подготовка показателей к анализу и визуализации.
  3. Преобразование результатов PIVOT в строки; исходные детали после агрегации при этом не восстанавливаются.

🧩 Синтаксис

SELECT ...
FROM source_table
UNPIVOT [INCLUDE NULLS | EXCLUDE NULLS] (
  measure_value FOR measure_name
  IN (column1 AS 'LABEL1', column2 AS 'LABEL2')
);

⚙️ Параметры и аргументы

measure_value — выходной столбец значений, measure_name — выходной столбец меток. IN перечисляет имена входных столбцов и необязательные метки AS. По умолчанию EXCLUDE NULLS исключает строки с NULL; INCLUDE NULLS сохраняет их.

Столбцы, объединяемые в один столбец значений, должны принадлежать одной группе типов, например числовой или символьной. UNPIVOT доступен с Oracle 11g; динамический пример ниже рассчитан на Oracle 19c.


💻 Примеры

Базовый пример

Показатели мастер-класса за 2026 год представлены четырьмя столбцами. За третий квартал данных нет:

WITH quarterly_fees (workshop_id, report_year, q1, q2, q3, q4) AS (
  SELECT 7, 2026, 120, 180, CAST(NULL AS NUMBER), 240 FROM dual
)
SELECT workshop_id, report_year, quarter_name, fee_amount
FROM quarterly_fees
UNPIVOT (
  fee_amount FOR quarter_name
  IN (q1 AS 'Q1', q2 AS 'Q2', q3 AS 'Q3', q4 AS 'Q4')
)
ORDER BY workshop_id, report_year, quarter_name;
WORKSHOP_ID REPORT_YEAR QUARTER_NAME FEE_AMOUNT
7 2026 Q1 120
7 2026 Q2 180
7 2026 Q4 240

Q3 отсутствует из-за EXCLUDE NULLS по умолчанию.

Несколько видов показателей и периодов

Из имени каждого столбца получим две метки — показатель и квартал:

WITH workshop_metrics (workshop_id, revenue_q1, revenue_q2, cost_q1, cost_q2) AS (
  SELECT 7, 300, 420, 180, 260 FROM dual
)
SELECT workshop_id, metric_name, period_name, metric_value
FROM workshop_metrics
UNPIVOT (
  metric_value FOR (metric_name, period_name)
  IN (revenue_q1 AS ('REVENUE', 'Q1'),
      revenue_q2 AS ('REVENUE', 'Q2'),
      cost_q1 AS ('COST', 'Q1'),
      cost_q2 AS ('COST', 'Q2'))
)
ORDER BY workshop_id, metric_name, period_name;
WORKSHOP_ID METRIC_NAME PERIOD_NAME METRIC_VALUE
7 COST Q1 180
7 COST Q2 260
7 REVENUE Q1 300
7 REVENUE Q2 420

Слева от AS стоят только входные столбцы, справа — строковые метки. Литерал квартала не добавляется к списку входных столбцов.

Обработка NULL

WITH studio_staff (studio_id, full_time, part_time, temporary) AS (
  SELECT 11, 4, CAST(NULL AS NUMBER), 2 FROM dual
  UNION ALL SELECT 12, 0, 3, NULL FROM dual
)
SELECT studio_id, staff_kind, staff_count
FROM studio_staff
UNPIVOT INCLUDE NULLS (
  staff_count FOR staff_kind
  IN (full_time AS 'FULL', part_time AS 'PART', temporary AS 'TEMP')
)
ORDER BY studio_id, staff_kind;
STUDIO_ID STAFF_KIND STAFF_COUNT
11 FULL 4
11 PART NULL
11 TEMP 2
12 FULL 0
12 PART 3
12 TEMP NULL

INCLUDE NULLS сохраняет все шесть строк; без него останутся четыре. Ноль не исключается.


Продвинутые техники

Динамический UNPIVOT

Список столбцов можно собрать из метаданных. В отдельной учебной схеме создадим таблицу с числовыми месяцами M01–M03:

CREATE TABLE demo_month_metrics (
  metric_id NUMBER,
  m01 NUMBER,
  m02 NUMBER,
  m03 NUMBER
);

INSERT INTO demo_month_metrics VALUES (1, 12, 18, NULL);

Следующий блок выбирает только числовые столбцы M01–M12, открывает курсор и действительно извлекает строки:

DECLARE
  v_columns VARCHAR2(4000);
  v_sql VARCHAR2(32767);
  v_cursor SYS_REFCURSOR;
  v_metric_id NUMBER;
  v_month_label VARCHAR2(3);
  v_metric_value NUMBER;
BEGIN
  SELECT LISTAGG('"' || column_name || '"', ', ')
         WITHIN GROUP (ORDER BY column_id)
  INTO v_columns
  FROM user_tab_columns
  WHERE table_name = 'DEMO_MONTH_METRICS'
    AND REGEXP_LIKE(column_name, '^M(0[1-9]|1[0-2])$')
    AND data_type = 'NUMBER';

  IF v_columns IS NULL THEN
    RAISE_APPLICATION_ERROR(-20001, 'No numeric month columns');
  END IF;

  v_sql := 'SELECT metric_id, month_label, metric_value '
        || 'FROM demo_month_metrics '
        || 'UNPIVOT (metric_value FOR month_label IN (' || v_columns || ')) '
        || 'ORDER BY metric_id, month_label';

  OPEN v_cursor FOR v_sql;
  LOOP
    FETCH v_cursor INTO v_metric_id, v_month_label, v_metric_value;
    EXIT WHEN v_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_metric_id || ': ' || v_month_label || '=' || v_metric_value);
  END LOOP;
  CLOSE v_cursor;
EXCEPTION
  WHEN OTHERS THEN
    IF v_cursor%ISOPEN THEN
      CLOSE v_cursor;
    END IF;
    RAISE;
END;
/
-- При включённом DBMS Output:
-- 1: M01=12
-- 1: M02=18

Фильтр не захватывает METRIC_ID и ограничивает имена заранее заданной формой. LISTAGG здесь собирает не более 12 коротких идентификаторов. Для динамического SELECT с несколькими строками используются OPEN FOR, FETCH и CLOSE; один EXECUTE IMMEDIATE без извлечения не выводит результат.

Комбинирование с PIVOT

WITH lesson_fees (room_id, kind, fee) AS (
  SELECT 10, 'CLAY', 80 FROM dual
  UNION ALL SELECT 10, 'CLAY', 120 FROM dual
  UNION ALL SELECT 10, 'PAPER', 60 FROM dual
  UNION ALL SELECT 20, 'CLAY', 90 FROM dual
), pivoted_fees AS (
  SELECT * FROM lesson_fees
  PIVOT (AVG(fee) FOR kind IN ('CLAY' AS clay_fee, 'PAPER' AS paper_fee))
)
SELECT room_id, lesson_kind, average_fee
FROM pivoted_fees
UNPIVOT (
  average_fee FOR lesson_kind
  IN (clay_fee AS 'CLAY', paper_fee AS 'PAPER')
)
ORDER BY room_id, lesson_kind;
ROOM_ID LESSON_KIND AVERAGE_FEE
10 CLAY 100
10 PAPER 60
20 CLAY 90

Две исходные суммы 80 и 120 стали средним 100. UNPIVOT меняет форму результата, но не восстанавливает эти исходные строки.

Агрегация после UNPIVOT

WITH studio_finance (kind, revenue, cost, profit) AS (
  SELECT 'CLAY', 100, 60, 40 FROM dual
  UNION ALL SELECT 'CLAY', 200, 120, 80 FROM dual
  UNION ALL SELECT 'PAPER', 90, 50, 40 FROM dual
)
SELECT kind, metric_name, AVG(metric_value) AS average_value
FROM studio_finance
UNPIVOT (
  metric_value FOR metric_name
  IN (revenue AS 'REVENUE', cost AS 'COST', profit AS 'PROFIT')
)
GROUP BY kind, metric_name
ORDER BY kind, metric_name;
KIND METRIC_NAME AVERAGE_VALUE
CLAY COST 90
CLAY PROFIT 60
CLAY REVENUE 150
PAPER COST 50
PAPER PROFIT 40
PAPER REVENUE 90

Все показатели здесь денежные. AVG вычисляется отдельно для каждого вида мастер-класса и показателя через GROUP BY.


Часто задаваемые вопросы

Как обработать разные типы данных?

Приведите значения к совместимому типу во входном подзапросе. В списке IN указываются имена его столбцов, а не вызовы TO_CHAR:

WITH mixed_metrics (item_id, price, opened_on, note_text) AS (
  SELECT 7, 42.5, DATE '2026-06-09', 'Clay' FROM dual
)
SELECT item_id, field_name, field_value
FROM (
  SELECT item_id,
         TO_CHAR(price, 'FM9990D00',
                 'NLS_NUMERIC_CHARACTERS = ''.,''') AS price_text,
         TO_CHAR(opened_on, 'YYYY-MM-DD') AS date_text,
         CAST(note_text AS VARCHAR2(40)) AS note_text
  FROM mixed_metrics
)
UNPIVOT (
  field_value FOR field_name
  IN (price_text AS 'PRICE', date_text AS 'DATE', note_text AS 'NOTE')
)
ORDER BY item_id, field_name;
ITEM_ID FIELD_NAME FIELD_VALUE
7 DATE 2026-06-09
7 NOTE Clay
7 PRICE 42.50

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

Сколько столбцов можно преобразовать?

Нужно учитывать ограничения числа столбцов таблицы и запроса для своей версии Oracle, совместимость типов и размер результата. Универсального практического правила «не более нескольких десятков» нет: большой список проверяют по плану и измерениям на своих данных.

Как преобразовать только часть столбцов?

Перечислите нужные столбцы в IN. Остальные поля сохранятся и повторятся для каждой полученной строки:

WITH kit_dimensions (kit_id, kind, price, weight, length_cm) AS (
  SELECT 7, 'CLAY', 45, 2.5, 30 FROM dual
)
SELECT kit_id, kind, metric_name, metric_value
FROM kit_dimensions
UNPIVOT (
  metric_value FOR metric_name
  IN (price AS 'PRICE', weight AS 'WEIGHT', length_cm AS 'LENGTH_CM')
)
ORDER BY kit_id, metric_name;

KIT_ID = 7 и KIND = CLAY сохраняются в трёх строках: LENGTH_CM = 30, PRICE = 45, WEIGHT = 2.5. Единицы измерения различны и определяются меткой.


Альтернативы UNPIVOT

UNION ALL

Для двух кварталов тот же принцип можно записать вручную; WHERE воспроизводит исключение NULL:

WITH quarterly_fees (workshop_id, q1, q2) AS (
  SELECT 7, 120, 180 FROM dual
  UNION ALL SELECT 8, NULL, 90 FROM dual
)
SELECT workshop_id, 'Q1' AS quarter_name, q1 AS fee_amount
FROM quarterly_fees WHERE q1 IS NOT NULL
UNION ALL
SELECT workshop_id, 'Q2' AS quarter_name, q2 AS fee_amount
FROM quarterly_fees WHERE q2 IS NOT NULL
ORDER BY workshop_id, quarter_name;
WORKSHOP_ID QUARTER_NAME FEE_AMOUNT
7 Q1 120
7 Q2 180
8 Q2 90

WITH и числовая метка

WITH quarterly_fees (workshop_id, q1, q2) AS (
  SELECT 7, 120, 180 FROM dual
  UNION ALL SELECT 8, NULL, 90 FROM dual
), long_fees AS (
  SELECT workshop_id, 1 AS quarter_no, q1 AS fee_amount
  FROM quarterly_fees WHERE q1 IS NOT NULL
  UNION ALL
  SELECT workshop_id, 2 AS quarter_no, q2 AS fee_amount
  FROM quarterly_fees WHERE q2 IS NOT NULL
)
SELECT workshop_id, DECODE(quarter_no, 1, 'Q1', 2, 'Q2') AS quarter_name,
       fee_amount
FROM long_fees
ORDER BY workshop_id, quarter_no;

Результат тот же: CTE отделяет формирование строк от замены кода через DECODE. UNION ALL сохраняет повторяющиеся строки; для аналога INCLUDE NULLS уберите условия IS NOT NULL.


Производительность и практика

  • Сравнивайте UNPIVOT и UNION ALL по фактическому плану и измерениям; универсального победителя по скорости нет.
  • Отбирайте нужные исходные строки и столбцы; преобразование может многократно увеличить число строк.
  • Используйте понятные метки и явно выбирайте поведение для NULL.
  • Для динамических запросов ограничивайте набор допустимых имён и действительно извлекайте результат курсора.

UNPIVOT подходит для подготовки отчётов и ETL, но само преобразование не отменяет агрегацию и не делает произвольную структуру автоматически нормализованной.

Источники: Oracle 19c: UNPIVOT, преобразования и агрегация, OPEN FOR, FETCH, новые возможности Oracle 11g.