Сведение данных в T-SQL

Работа с базами данных в .NET Framework --- Оконные функции T-SQL --- Сведение данных

Сведение (pivoting) является методикой агрегирования и поворота данных из строк в столбцы. При сведении нужно определить три элемента: элемент, который нужно видеть в строках (элемент группировки), элемент, который нужно видеть в столбцах (элемент распределения) и элемент, который нужно видеть в разделе данных (элемент агрегирования).

В качестве примера представьте, что нужно получить представление Sales.OrderValues и вернуть по строке на каждый год, по строке на каждый месяц и общую сумму всех сумм заказов для пересечений годов и месяцев. В этом запросе элементом группировки по строкам является YEAR(orderdate), элементом распределения по столбцам является MONTH(orderdate), уникальными значениями распределения являются 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 и 12, а элемент данных, или агрегации, является SUM (val).

Для получения сведения нужно прежде всего подготовить табличное выражение, такое как CTE, которое возвращает только три элемента, задействованные в задаче сведения. Затем во внешней инструкции запрашивается табличное выражение и используется оператор для обработки логики сведения (выходные данные выровнены):

WITH C AS
(
  SELECT YEAR(orderdate) AS orderyear, MONTH(orderdate) AS ordermonth, val
  FROM Sales.OrderValues
)
SELECT *
FROM C
  PIVOT(SUM(val)
    FOR ordermonth IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS P;

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

Решение заключается в использовании функции R0W_NUMBER, которая присваивает порядковые номера в секциях клиентов с использованием нужного упорядочения — в нашем случае по orderdate DESC и orderid DESC. Затем атрибут, содержащий номер строки, может использоваться как элемент распределения, и порядковые номера могут вычисляться как значения распределения.

Поэтому для начала приведу код, который генерирует номера строк клиентских заказов с самого последнего до самого старого:

SELECT custid, val,
  ROW_NUMBER() OVER(PARTITION BY custid
                    ORDER BY orderdate DESC, orderid DESC) AS rownum
FROM Sales.OrderValues;

Теперь можно определить выражение CTE на основе этого запроса, а затем во внешнем запросе организовать логику сведения с использованием rownum в качестве элемента распределения:

WITH C AS
(
  SELECT custid, val,
    ROW_NUMBER() OVER(PARTITION BY custid
                      ORDER BY orderdate DESC, orderid DESC) AS rownum
  FROM Sales.OrderValues
)
SELECT *
FROM C
  PIVOT(MAX(val) FOR rownum IN ([1],[2],[3],[4],[5])) AS P;

Если для каждого клиента нужно конкатенировать в одну строку идентификаторы последних пяти заказов, можно воспользоваться появившейся в SQL Server 2012 функцией CONCAT:

WITH C AS
(
  SELECT custid, CAST(orderid AS VARCHAR(11)) AS sorderid,
    ROW_NUMBER() OVER(PARTITION BY custid
                      ORDER BY orderdate DESC, orderid DESC) AS rownum
  FROM Sales.OrderValues
)
SELECT custid, CONCAT([1], ','+[2], ','+[3], ','+[4], ','+[5]) AS orderids
FROM C
  PIVOT(MAX(sorderid) FOR rownum IN ([1],[2],[3],[4],[5])) AS P;

Функция CONCAT автоматически заменяет NULL на пустую строку. Для получения такою же результата в более ранних версиях SQL Server придется воспользоваться оператором конкатенации "+" и функцией COALESCE, которая заменит значения NULL на пустую строку:

WITH C AS
(
  SELECT custid, CAST(orderid AS VARCHAR(11)) AS sorderid,
    ROW_NUMBER() OVER(PARTITION BY custid
                      ORDER BY orderdate DESC, orderid DESC) AS rownum
  FROM Sales.OrderValues
)
SELECT custid, 
  [1] + COALESCE(','+[2], '')
      + COALESCE(','+[3], '')
      + COALESCE(','+[4], '')
      + COALESCE(','+[5], '') AS orderids
FROM C
  PIVOT(MAX(sorderid) FOR rownum IN ([1],[2],[3],[4],[5])) AS P;