Оператор UNPIVOT

Оператор UNPIVOT по смыслу противоположен оператору PIVOT. Как было описано в предыдущей статье, оператор PIVOT преобразует нормализованную таблицу в таблицу, у которой столбцами являются значения в исходной таблице. Оператор UNPIVOT берет сводную таблицу и преобразует ее обратно к нормализованной форме с одной строкой на запись данных, используя в результате имена столбцов в качестве значений. Например, предположим, что у нас есть следующая таблица с данными:

CREATE TABLE PIVOT_Sales(EmpId INT, [2005] MONEY, [2006] MONEY, [2007] MONEY)
INSERT PIVOT_Sales VALUES(1, 12000, 18000, 25000)
INSERT PIVOT_Sales VALUES(2, 15000, 6000, NULL)
INSERT PIVOT_Sales VALUES(3, NULL, 20000, 24000)

Это похоже на результат операции PIVOT из предыдущих двух статей. Для каждого сотрудника имеется по одной строке данных о продажах за три года, каждый год в своей колонке. Если не было данных о продажах для конкретного сотрудника за конкретный год, просто вставляется значение NULL. Мы можем преобразовать эту таблицу обратно к её виду в предыдущей статье, где имеется по одной строке для сотрудника за каждый год его продаж, используя следующую инструкцию UNPIVOT:

SELECT EmpId, CAST (Yr AS INT) AS Yr, Sales
FROM (SELECT EmpId, [2005], [2006], [2007] FROM PIVOT_Sales) AS p
UNPIVOT (Sales FOR Yr IN ([2005], [2006], [2007])) AS s

Здесь столбец Yr явно приведён к целому числу. По умолчанию тип для сводного столбца был бы NVARCHAR(128); длинна выбрана такой, какой может быть максимальная длинна имени столбца - 128 символов.

В результате получается:

EmpId       Yr          Sales
----------- ----------- ---------------------
1           2005        12000.00
1           2006        18000.00
1           2007        25000.00
2           2005        15000.00
2           2006        6000.00
3           2006        20000.00
3           2007        24000.00

В отличие от операций PIVOT, которые могут быть необратимыми, все операции UNPIVOT обратимы (при условии сохранения всех входных данных). То есть мы всегда можем преобразовать выходные данные операции UNPIVOT обратно в исходную таблицу, используя соответствующую операцию PIVOT. Также оператор UNPIVOT не требует и не поддерживает функции агрегирования.

Давайте посмотрим на план приведенного выше запроса:

|--Compute Scalar(DEFINE:([Expr1010]=CONVERT(int,[Expr1009],0)))
       |--Filter(WHERE:([Expr1008] IS NOT NULL))
            |--Nested Loops(Left Outer Join, OUTER REFERENCES:([PIVOT_Sales].[2005], [PIVOT_Sales].[2006], [PIVOT_Sales].[2007]))
                 |--Compute Scalar(DEFINE:([PIVOT_Sales].[EmpId]=[PIVOT_Sales].[EmpId]))
                 |    |--Table Scan(OBJECT:([PIVOT_Sales]))
                 |--Constant Scan(VALUES:((N'2005',[PIVOT_Sales].[2005]),(N'2006',[PIVOT_Sales].[2006]),(N'2007',[PIVOT_Sales].[2007])))

Этот план запроса каждую строку входной таблицы объединяет со считанными константами, создавая строки — по одной для каждого из столбцов, перечисленных в предложении UNPIVOT IN. Затем отфильтровываются все строки, содержащие NULL (обратите внимание, что [Expr1008] это столбец Sales, а [ Expr1009 ] это столбец Yr). В этом плане запроса стоит отметить несколько моментов. Во-первых, тут должно быть соединение Nested Loops, поскольку оператор сканирования констант для создания строк использует коррелированные параметры со внешней стороной соединения. Невозможно получить эти строки без этих коррелированных параметров. Во-вторых, соединение не обязательно должно быть левым внешним соединением. Постоянное сканирование всегда производит ровно три строки, и, таким образом, внешние строки всегда будут участвовать в соединении и никогда не приведут к появлению NULL. Тем не менее внешнее соединение в этом контексте безвредно и ведет себя как внутреннее соединение.

Обратите внимание, что мы можем переписать запрос так:

SELECT p.EmpId, Yr, Sales
FROM PIVOT_Sales AS p CROSS APPLY
    (
    SELECT EmpId, 2005 AS Yr, [2005] AS Sales UNION ALL
    SELECT EmpId, 2006, [2006] UNION ALL
    SELECT EmpId, 2007, [2007]
    ) AS s
WHERE Sales IS NOT NULL

Этот запрос имеет почти такой же план запроса. Синтаксис UNION ALL дает результат, аналогичный сканированию констант, за исключением того, что теперь есть три считывания констант и оператор конкатенации:

  |--Filter(WHERE:([Union1007] IS NOT NULL))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([p].[2005], [p].[2006], [p].[2007]))
            |--Table Scan(OBJECT:([tempdb].[dbo].[PIVOT_Sales] AS [p]))
            |--Concatenation
                 |--Constant Scan(VALUES:(((2005))))
                 |--Constant Scan(VALUES:(((2006))))
                 |--Constant Scan(VALUES:(((2007))))

В этом плане [Union1007] — это столбец Sale. На самом деле мы можем увидеть определение [Union1007] из столбца DefinedValues оператора конкатенации при использовании SET SHOWPLAN_ALL ON. Значения для [Union1007] на самом деле получены непосредственно из коррелированных параметров CROSS APPLY (из таблицы PIVOT_Sales), а не из считанных констант. [Union1006] — это столбец Yr, и значения получены в результате сканирования констант.