Решение задачи. Транспонирование данных

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

Итоговый запрос

with exmpl as
 (select *
    from table(tt_tbl_row(tr_tbl_row(1,
                                     tt_dml(tr_dml('NAME', 'Иван'),
                                            tr_dml('SURNAME', 'Иванов'),
                                            tr_dml('MIDDLE_NAME', 'Иванович'),
                                            tr_dml('AGE', '45'),
                                            tr_dml('GENDER', 'M'))),
                          tr_tbl_row(2,
                                     tt_dml(tr_dml('NAME', 'Петр'),
                                            tr_dml('SURNAME', 'Петров'),
                                            tr_dml('MIDDLE_NAME', 'Петрович'),
                                            tr_dml('AGE', '35'),
                                            tr_dml('GENDER', 'M'))),
                          tr_tbl_row(3,
                                     tt_dml(tr_dml('NAME', 'Михаил'),
                                            tr_dml('SURNAME', 'Иванов'),
                                            tr_dml('MIDDLE_NAME', 'Михайлович'),
                                            tr_dml('AGE', '25'),
                                            tr_dml('GENDER', 'M'))))))
select *
  from (select e.id
              ,v.name
              ,v.value
          from exmpl e
         cross join table(e.t_row_val) v)
pivot (max(value) for name in('NAME' as name,
                         'SURNAME' as surname,
                         'MIDDLE_NAME' as middle_name,
                         'AGE' as age,
                         'GENDER' as gender))
 where upper(surname) like 'ИВАНОВ';

Объяснение

Имеем коллекцию, состоящую из id и вложенной таблицы key - value

Первым шагом необходимо плоско развернуть вложенную таблицу, чтобы получить данные вида

|id | name |value |
|—-|—————|————|
|1 |NAME | Иван |
|1 |SURNAME |Иванов |

Для этого используем CROSS JOIN (так как надо соединить все строки вложенной таблицы с ее id) и функцию table(), позволяющую работать с вложенной таблицей так, как будто это обычная таблица

В полученном результате названия полей - значения в колонке name, что не позволяет фильтровать по ним данные.

Нам заранее известны названия полей, что позволяет транспонировать матрицу результатов предыдущего шага при помощи оператора PIVOT. По условию задачи данные валидны, поэтому можем использовать агрегатную функцию MAX (или любую другую, которая выведет нам значения из колонки value) внутри оператора PIVOT.

Транспонировав результат, остается только отфильтровать данные (с учетом регистра строки в поле SURNAME)

Обсудить в чатике 💬

Коллеги, накидали вариантов в чате, тоже полезно заценить решения 😉

👍, если понравилась задачка.

Автор: Константин Андронов
#решениезадачи #pivot #транспонирование
Oracle Developer