Трансформация результирующих наборов данных. Операторы PIVOT и UNPIVOT.

     Иногда при работе с БД может возникнуть необходимость изменения порядка отображения результирующего набора данных (результата выполнения выражения возвращающего табличное значение). Например, таблицу-результат работы оператора выборки или результат выполнения пользовательской функции можно экспортировать в  *.csv файл и далее работать с ним используя MS Excel.

      Зачем может понадобиться изменять набор данных перед выполнением операции экспорта? Как вы увидите далее на примере, проектирование реляционных БД имеет свои нюансы и, зачастую, то, как данные содержатся в таблицах БД диаметрально противоположно тому, как они должны содержаться в таблицах Excel или Word для обеспечения удобства работы с ними.

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

Скриншот 1.

      Лично мне было удобно использовать данные в виде 1-го результирующего набора для построения диаграмм в MS Word. Но что делать, если есть необходимость отобразить их в виде привычной многим таблицы (в виде 2-го результирующего набора на скриншоте)?

      Очевидно, что их необходимо своего рода “транспонировать” по аналогии с операцией транспонирования матрицы, но, как видите это должно быть не совсем транспонирование в его традиционном понимании.

      Для выполнения таких задач служит оператор Transact-SQL – PIVOT. Как написано MSDN (http://msdn.microsoft.com/ru-ru/library/ms177410.aspx): ”Оператор PIVOT разворачивает возвращающее табличное значение выражение, преобразуя уникальные значения одного столбца выражения в несколько выходных столбцов, а также, в случае необходимости, объединяет оставшиеся повторяющиеся значения столбца и отображает их в выходных данных.”

      Конкретно для нашего примера мы видим, что значения столбца “period_id” после выполнения оператора PIVOT преобразуются в поля результирующего набора, значения этих полей берутся из исходного столбца “value” и все это группируется по полю “element_name”. Важно отметить, что в операторе PIVOT я использовал агрегатную функцию MAX(). Использование этой функции в данном случае обусловлено лишь синтаксическими особенностями оператора PIVOT, которые требуют использования статистической функции.

      Как выполнить обратное по отношению к результату, полученному с использованием оператора PIVOT, преобразование результирующего набора данных, когда необходимо “транспонировать” столбцы таблицы-результата выполнения запроса в строки? Для проведения подобных манипуляций используется оператор UNPIVOT.

Скриншот 2.

      На 2-ом скриншоте видно, что результатом выполнения 2-ой инструкции SELECT, в которой использован оператор UNPIVOT, является набор данных, в котором поля исходного набора (результата выполнения UDF): pid1…4 - стали соответствующими строками сформированного поля “pids”. Также появилось поле “ods”, содержащее прежние значения столбцов pid1…4.

      Для полной ясности еще одна цитата из MSDN (по тому же референсу): “оператор UNPIVOT не является в точности обратным оператору PIVOT. Оператор PIVOT производит статистическую обработку и слияние нескольких строк в единую выходную строку. Оператор UNPIVOT не восстанавливает исходные возвращающие табличное значение выражения, так как строки были объединены”.