Классика баз данных - статьи



         

Функции PIVOT и UNPIVOT


Магазин ноутбуков с успехом использует демонстрационную базу данных в течение многих лет, и накопил огромную статистику по продажам ноутбуков. Естественно желание знать, для сравнения, объемы продаж за разные годы и общую сумму прибыли. Для того, чтобы из таблиц Orders и Products получить интересующую владельцев магазина информацию лучшим способом является использование ключевого функции PIVOT, позволяющей как бы "развернуть" данные в таблице. SELECT Model, [2005], [2004] FROM ( SELECT P.Model, DATEPART(year, O.[Date]) AS [Year], O.Quantity FROM Orders O INNER JOIN Products P ON P.ProductID = O.ProductID ) AS C PIVOT (SUM(Quantity) FOR [Year] IN ([2005], [2004])) AS PVT

С использованием виртуального представления код можно написать несколько иначе: WITH C(Model, [Year], Quantity) AS ( SELECT P.Model, DATEPART(year, O.[Date]) AS [Year], O.Quantity FROM Orders O INNER JOIN Products P ON P.ProductID = O.ProductID )

SELECT Model, [2005], [2004] FROM C PIVOT (SUM(Quantity) FOR [Year] IN ([2005], [2004])) AS PVT

Результатом выполнения данного кода в демонстрационной базе данных будет таблицу с тремя колонками: Model, 2005 и 2004. Например: Model 2005 2004 ----------------------------------- A75-S206 10 24 M40-110 17 38 S215SR 2 10 T2XRP 35 12 V6800V 12 4

В предыдущих версиях SQL Server, где не была реализована функция PIVOT и CTE, чтобы достичь требуемого результата, пришлось бы писать код вроде приведенного ниже. SELECT C.Model, SUM(CASE C.[Year] WHEN 2005 THEN C.Quantity ELSE 0 END) AS [2005], SUM(CASE C.[Year] WHEN 2004 THEN C.Quantity ELSE 0 END) AS [2004] FROM ( SELECT P.Model, DATEPART(year, O.[Date]) AS [Year], O.Quantity FROM Orders O INNER JOIN Products P ON P.ProductID = O.ProductID ) AS C GROUP BY C.Model

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


Содержание  Назад  Вперед