В этой статье мы продолжим тему логической обработки запросов. В предыдущих материалах серии были рассмотрены табличные операторы JOIN, APPLY и PIVOT предложения FROM. На этот раз речь пойдет о четвертом и последнем табличном операторе предложения FROM — UNPIVOT.

В качестве тестовых данных будет использоваться та же база данных, TSQLV4, которая применялась в предыдущих статьях. Если она еще не установлена, вы можете загрузить программный код для ее создания и заполнения данными по адресу: http://tsql.solidq.com/SampleDatabases/TSQLV4.zip. Убедитесь, что установлен контекст для этой базы данных, прежде чем запускать примеры программного кода из статьи:

USE TSQLV4;

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

Обратное транспонирование данных

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

 

Содержимое таблицы Sales.MyPivotedOrders

 

В ходе операции обратного транспонирования обрабатывается любое число исходных столбцов, превышающее единицу. Они преобразуются в два целевых столбца, в одном из которых содержатся значения исходного столбца (values column), в другом — имена исходных столбцов (names column).

В данном примере источник содержит строку для каждого сотрудника и столбец для года заказа, и в каждом пересечении столбца и строки хранятся итоговые значения заказа для текущего сотрудника и года. Работать с данными в их текущем состоянии неудобно, стоит преобразовать их в отдельные строки для каждого сотрудника и года. В таблице 2 показан желаемый результат.

 

Результат задачи обратного транспонирования

 

Аналогично оператору PIVOT, T-SQL поддерживает оператор, именуемый UNPIVOT, который также используется в предложении FROM. Как и PIVOT, оператор UNPIVOT выполняет последовательность логических шагов. Я обозначаю шаги формулой 1-UN, где 1 отражает то обстоятельство, что предложение FROM представляет собой первое важное предложение, обрабатываемое логически, U — сокращение от UNPIVOT, а N — номер шага в операторе UNPIVOT.

Оператор UNPIVOT применяет три логических шага:

  • 1-U1. Создание копий.
  • 1-U2. Извлечение элемента.
  • 1-U3. Удаление значений NULL.

В следующих разделах показано применение этих шагов для нашей тестовой задачи.

1-U1. Создание копий

Первый шаг к обратному транспонированию данных заключается в формировании отдельного экземпляра каждой исходной строки для каждого столбца без транспонирования. Наши столбцы без транспонирования представляют годы заказов: 2014, 2015 и 2016, поэтому на данном шаге формируется копия для каждого года. В таблице 3 показан результат этого шага для нашей тестовой задачи.

 

Результат шага 1-U1

 

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

1-U2. Извлечение элемента

На втором шаге, по сути, создается столбец значений (в нашем примере ему назначено имя val). Извлекается элемент из исходных столбцов, соответствующих именам, которые представлены текущей копией. В нашем примере извлекается значение из исходного столбца года, который соответствует текущему значению orderyear. Если orderyear — 2014, val возвращает значение из столбца [2014], если orderyear — 2015, val возвращает значение из [2015], а когда orderyear — 2016, val возвращает значение из [2016]. В таблице 4 показан желаемый результат этого шага для нашей тестовой задачи.

 

Результат шага 1-U2

 

1-U3. Удаление значений NULL

Третий и последний шаг обратного транспонирования — удалить строки со значениями NULL в результирующем столбце значений. Обычно хранить эти строки не требуется, поскольку они представляют неприменимые случаи. В нашем примере это случаи, когда сотрудник бездействовал в течение заданного года.

Выходные данные этого шага в нашем примере были показаны в таблице 2 как желаемый вывод задачи обратного транспонирования.

Оператор UNPIVOT

Как отмечалось, оператор UNPIVOT построен, подобно оператору PIVOT, как шаг в предложении FROM. Его синтаксис требует указать три упомянутых выше элемента, участвующих в задаче обратного транспонирования.

  1. Целевое имя столбца, в котором будут храниться значения исходного столбца (в нашем случае val).
  2. Целевое имя столбца, в котором будут храниться имена исходного столбца (в нашем случае orderyear).
  3. Имена исходных столбцов (в нашем случае [2014], [2015], [2016]).

Вы указываете эти элементы в операторе UNPIVOT с использованием следующего синтаксиса:

SELECT *
   FROM 
           UNPIVOT( 1 FOR 2 IN (3) )
           AS ;

В применении к нашим данным полный запрос с оператором UNPIVOT выглядит следующим образом:

SELECT empid, orderyear, val
FROM Sales.MyPivotedOrders
  UNPIVOT( val FOR orderyear
  IN ([2014],[2015],[2016]) ) AS U;

На рисунке 1 показано, как элементы, участвующие в операторе UNPIVOT, соответствуют входным и выходным данным оператора.

 

Оператор UNPIVOT
Рисунок 1. Оператор UNPIVOT

 

Обратите внимание, что столбец имен (orderyear) вводится как NVARCHAR (128). Если требуется другой тип, например INT, следует выполнить его приведение.

На рисунке 2 показана блок-схема, описывающая логическую обработку запроса предложения FROM, со всеми четырьмя табличными операторами, в том числе оператором UNPIVOT.

 

Блок-схема логической обработки запроса — предложение FROM
Рисунок 2. Блок-схема логической обработки запроса — предложение FROM

 

Альтернативное решение с использованием APPLY

Оператор UNPIVOT построен очень изящно и отличается лаконичностью, но у него есть недостатки. Один из них — невозможность (по крайней мере, простым способом) обратного транспонирования нескольких наборов исходных столбцов в несколько целевых столбцов значений. Представьте, например, что у вас есть один набор столбцов, представляющих значения заказов, и другой, представляющий количества заказов. С другой стороны, вы можете быть не заинтересованы в удалении строк со значениями NULL в столбце значений. В листинге 2 приводится альтернативное решение обратного транспонирования с использованием оператора APPLY для нашей тестовой задачи.

В решении используется оператор CROSS APPLY с конструктором табличного значения для определения трех строк для трех лет заказов, чтобы формировать копии (шаг 1).

Во второй столбец в каждой из трех строк извлекается элемент из исходной строки года, соответствующий текущему году заказа (шаг 2). Вы можете выполнить первый шаг с помощью CROSS JOIN, но не второй. Помните, что объединение воспринимает свои два списка входных данных как набор, поэтому нельзя ссылаться с одной стороны на элементы другой. И наоборот, CROSS APPLY оценивает входные данные слева направо и потому позволяет входным данным справа ссылаться на данные слева. Чтобы обеспечить поддержку нескольких величин (например, val и qty), просто укажите больше столбцов в каждой строке.

Наконец, поскольку APPLY создает псевдоним столбца значений (в нашем случае val) в предложении FROM, можно ссылаться на этот псевдоним в предложении WHERE, если вы хотите удалить строки со значениями NULL. Очевидно, что удалять значения NULL не обязательно.

Чтобы показать, как этот метод работает при обратном транспонировании нескольких наборов столбцов, я воспользуюсь таблицей с именем MyPivotedOrders2, которую можно создать и заполнить данными с помощью программного кода в листинге 3.

В таблице 5 показано содержимое MyPivotedOrders2.

 

Содержимое таблицы Sales.MyPivotedOrders2

 

Требуется создать два столбца значений: из val2014, val2015, val2016 необходимо создать целевой столбец с именем val, а из qty2014, qty2015, qty2016 — целевой столбец с именем qty. Как отмечалось, для каждой целевой величины просто добавьте в каждую строку в конструкторе табличных значений столбец, соответствующий этой величине, например, как в листинге 4.

Выходные данные, формируемые этим запросом, показаны в таблице 6.

 

Результат обратного транспонирования с несколькими показателями

 

Сочетание операторов

Помните, что вы можете сочетать табличные операторы в предложении FROM. С точки зрения логической обработки запросов они обрабатываются слева направо в том порядке, в котором записаны. Это означает, что результаты одного оператора становятся входными данными для следующего. Например, предположим, что требуется транспонировать данные из таблицы Sales.MyPivotedOrders, и, вместо того чтобы показать сотрудников в строках, а годы заказов в столбцах, вы хотите возвратить годы заказов в строках, а идентификаторы сотрудников — в столбцах. В таблице 7 показаны необходимые выходные данные.

 

Желаемый результат задачи транспонирования

 

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

Динамическая операция UNPIVOT

В предыдущей статье было показано, как использовать метод на основе параметра FOR XML, чтобы построить запрос PIVOT и выполнить его динамически, избегая жесткого распределения значений. Очень похожим способом можно выполнить задачу динамического обратного транспонирования. В нашем примере с таблицей N’Sales.MyPivotedOrders вы можете получить имена столбцов, для которых нужно выполнить обратное транспонирование путем запроса к представлению sys.columns. Вы можете выбрать все столбцы, кроме столбца empid. В остальном все происходит так же, как было показано в задаче динамического транспонирования. Метод FOR XML используется для создания списка имен столбцов с разделительными запятыми, который должен появиться в предложении IN оператора UNPIVOT и конкатенации статических частей запроса перед списком столбцов и после него. Наконец используется хранимая процедура sp_executesql для запуска кода.

В листинге 6 приводится полный программный код решения.

Итак, в этой статье я остановился на логической обработке запросов табличного оператора UNPIVOT. Были описаны три этапа работы оператора: создание копий, извлечение элемента и удаление значений NULL; представлены синтаксис оператора и рекомендации, как запомнить элементы этого синтаксиса. Мы рассмотрели недостатки оператора, альтернативное решение с использованием оператора APPLY, сочетание операторов PIVOT и UNPIVOT и, наконец, динамическое обратное транспонирование.

Листинг 1. Построение таблицы MyPivotedOrders и заполнение ее данными
IF OBJECT_ID(N'Sales.MyPivotedOrders', N'U') IS NOT NULL DROP TABLE Sales.MyPivotedOrders;
-- В SQL Server 2016 используйте: DROP TABLE IF EXISTS Sales.MyPivotedOrders;
GO

WITH C AS
(
  SELECT empid, YEAR(orderdate) AS orderyear, val
  FROM Sales.OrderValues
  WHERE custid = 5
) 
SELECT *
INTO Sales.MyPivotedOrders
FROM C
  PIVOT( SUM(val)
    FOR orderyear IN ([2014], [2015], [2016]) ) AS P;

ALTER TABLE Sales.MyPivotedOrders
  ADD CONSTRAINT PK_MyPivotedOrders PRIMARY KEY(empid);

SELECT *
FROM Sales.MyPivotedOrders;
Листинг 2.  Альтернативное решение обратного транспонирования с использованием оператора APPLY 
SELECT empid, orderyear, val
FROM Sales.MyPivotedOrders
  CROSS APPLY ( VALUES(2014, [2014]),
                      (2015, [2015]),
                      (2016, [2016]) ) AS A(orderyear, val)
WHERE val IS NOT NULL;
Листинг 3. Обратное транспонирование нескольких наборов столбцов
IF OBJECT_ID(N'Sales.MyPivotedOrders2', N'U') IS NOT NULL
DROP TABLE Sales.MyPivotedOrders2;
-- В SQL Server 2016 используйте: DROP TABLE IF EXISTS Sales.MyPivotedOrders2;
GO

SELECT empid,
SUM(CASE WHEN orderyear = 2014 THEN val END) AS val2014,
SUM(CASE WHEN orderyear = 2015 THEN val END) AS val2015,
SUM(CASE WHEN orderyear = 2016 THEN val END) AS val2016,
SUM(CASE WHEN orderyear = 2014 THEN qty END) AS qty2014,
SUM(CASE WHEN orderyear = 2015 THEN qty END) AS qty2015,
SUM(CASE WHEN orderyear = 2016 THEN qty END) AS qty2016
INTO Sales.MyPivotedOrders2
FROM Sales.OrderValues
CROSS APPLY ( VALUES(YEAR(orderdate)) ) AS A(orderyear)
WHERE custid = 5
GROUP BY empid;
ALTER TABLE Sales.MyPivotedOrders2
ADD CONSTRAINT PK_MyPivotedOrders2 PRIMARY KEY(empid);
SELECT *
FROM Sales.MyPivotedOrders2; 
Листинг 4. Добавление столбца
SELECT empid, orderyear, val, qty
FROM Sales.MyPivotedOrders2
  CROSS APPLY ( VALUES(2014, [val2014], [qty2014]),
                      (2015, [val2015], [qty2015]),
                      (2016, [val2016], [qty2016]) ) AS A(orderyear, val, qty)
WHERE val IS NOT NULL OR qty IS NOT NULL;
Листинг 5. Сведение результатов
SELECT *
FROM Sales.MyPivotedOrders
  UNPIVOT( val FOR orderyear IN ([2014],[2015],[2016]) ) AS U
  PIVOT( MAX(val) FOR empid IN([1], [2], [3], [4], [5], [8], [9]) ) AS P;
Листинг 6. Полный программный код обратного транспонирования
DECLARE
  @cols AS NVARCHAR(1000),
  @sql  AS NVARCHAR(4000);

-- Конструирование списка столбцов для предложения IN
SET @cols = STUFF(
  (SELECT N','+ QUOTENAME(name) AS [text()]
   FROM sys.columns
   WHERE object_id = OBJECT_ID(N'Sales.MyPivotedOrders', N'U')
     AND name NOT IN(N'empid')
   ORDER BY name
   FOR XML PATH(''), TYPE).value('.[1]','NVARCHAR(MAX)'),
  1, 1, N’’);

-- Конструирование полной инструкции T-SQL
-- и динамическое выполнение
SET @sql = N’SELECT empid, orderyear, val
FROM Sales.MyPivotedOrders
  UNPIVOT(val FOR orderyear IN(' + @cols + N')) AS U;';

EXEC sys.sp_executesql @stmt = @sql;