Максим Чалышев - Oracle SQL. 100 шагов от новичка до профессионала

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

Для транспонирования таблицы с группировкой в ORACLE SQL используется специальный оператор PIVOT.

Синтаксис конструкции:

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

СОЗДАДИМ ДЕМО-ТАБЛИЦУ.

Условимся, что перечень значений свойств был ограничен.

Рисунок 142. Использование PIVOT

И перепишем уже запрос с использованием PIVOT.

Рисунок 143. Запрос с группировкой: демонстрация PIVOT

Проделаем то же самое с таблицей AUTO.

Рисунок 144. Демонстрация использования AUTO

Удалим тестовую таблицу t1.

Для функции PIVOT существует обратная функция UNPIVOT — для преобразования строки запроса в столбец.

Какие агрегатные функции мы можем использовать в конструкции PIVOT?

Такие же, как и в запросах с группировкой GROUP BY. Это могут быть SUM, COUNT, MIN, MAX, AVG.

Как задать свои наименования столбцов?

После оператора FOR можно задавать свои наименования столбцов через инструкцию AS.

Пример:

1. Сделайте то же самое, только чтобы в первом столбце были цвета, а в названиях полей — названия геометрических фигур, то есть транспонирование по названию фигур геометрии.

2. Повторите с использованием PIVOT запрос с марками авто AUTO (MARK).

Язык SQL позволяет программировать специальные последовательности целых чисел (итераторы), которые могут использоваться в запросах.

Создание итераторов реализуется с помощью конструкции CONNECT BY.

Синтаксис

В переменную LEVEL будут выведены последовательно значения от 1 до N.

Простой пример: выведем в запросе все числа от 1 до 100.

То есть специальное ключевое слово LEVEL выполняет роль последовательности от 0 до ограничения LEVEL <101.

Рисунок 145. Демонстрация работы итератора

Пример итератора Connect By для работы с датами на ORACLE SQL.

Вывести календарь на следующие 30 дней.

Здесь level применяется одновременно с функцией SYSDATE.

Рисунок 146. Использование итераторов

Вывести календарь на следующие 30 дней, без субботы и воскресенья.

Рисунок 147. Использование итераторов

Вывести числа от 1 до 100, кроме чисел, которые делятся на 3 нацело.

Рисунок 148. Использование итератора MOD

Данную конструкцию итератора CONNECT BY можно использовать в сочетании с другими запросами SQL, например:

Рисунок 149. Использование итератора

Иногда это может быть удобно и востребовано на практике.

Я пишу следующий запрос: SELECT level FROM DUAL CONNECT BY (Level <11 and level> 5), но результат неправильный, выводится только одна строка, почему так происходит?

Подобный синтаксис, действительно, хоть и не вызывает ошибку, но приводит к некорректным результатам.

Попробуйте сделать следующим образом:

Чем отличается Sequence от итераторов в запросах с CONNECT BY?

Это совершенно разные сущности, хотя результат их работы в чем-то схож. SEQUENCE представляет собой реальный физический объект в базе данных, обращение к которому позволяет вывести на экран заданную последовательность значений, запрос CONNECT BY LEVEL — это просто способ сделать почти то же самое в запросе, без создания SEQUENCE.

1. Вывести с помощью запроса значение sin числа от 0 до 1 с шагом 0,1.

2. Вывести с помощью запроса число и день недели (TO_CHAR (DATE,«DY»)) всех дней за 2008 год.

3. Вывести с помощью запроса все числа от 500 до 1000, кроме тех чисел, которые делятся нацело на 10.