Функции 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 является лишь синтаксической оболочкой для приведенной выше конструкции.
Посмотрев планы исполнения примера, приведенного выше и примера, с использованием функции PIVOT, можно убедиться в их идентичности.
Функция UNPIVOT выполняет процедуру обратную PIVOT, "разворачивая" обратно таблицу данных, подвергшихся "обработке" функцией PIVOT , в исходное состояние. Положим, что для ведения статистики имеется следующая таблица CREATE TABLE [Statistics]( [Model] [nvarchar](32) NOT NULL, [2005] [int] NOT NULL, [2004] [int] NOT NULL
)
содержащая данные, полученные в ходе выполнения предыдущего запроса – примера использования функции PIVOT. SELECT * FROM [Statistics] UNPIVOT(TotalQuantity FOR [Year] IN ([2005], [2004])) AS PVT
Результатом выполнения будет таблица трех из столбцов: Model, TotalQuantity, Year. Model TotalQuantity Year ------------------------------------------ A75-S206 10 2004 A75-S206 24 2005 M40-110 17 2004 M40-110 38 2005
