Как транспонировать данные SQL Server с помощью PIVOT

Оператор PIVOT в SQL Server позволяет преобразовывать строки в столбцы, преобразуя данные из вертикально ориентированную форму в горизонтально ориентированную форму. Это может быть полезно при суммировании данных на основе нескольких атрибутов.

Пример данных и желаемый результат

Рассмотрим следующий начальный набор данных:

SELECT Name1, Name2, Value FROM mytable

Name1 | Name2 | Value
------- ------- ------
A      | P1     | 1
A      | P2     | 1
A      | P3     | 2
B      | P1     | 3
B      | P2     | 1
B      | P4     | 1

Желаемый результат — свести данные так, чтобы оператор PIVOT преобразовывал значения столбца Name1 в заголовки столбцов, а значения из столбца Value суммировались по столбцу Name2.

Транспонирование данных с помощью PIVOT в SQL Server 2005

Для SQL Server 2005 можно использовать оператор PIVOT следующим образом:

DECLARE @cols VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)

SELECT  @cols = STUFF(( SELECT distinct  ','   QuoteName([Name1])
                        FROM myTable FOR XML PATH('') ), 1, 1, '') 


SET @sqlquery = 'SELECT * FROM
      (SELECT Name2, Name1, Value
       FROM myTable ) base
       PIVOT (Sum(Value) FOR [Name1]
       IN ('   @cols   ')) AS finalpivot'

EXECUTE ( @sqlquery )

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

P1      P2      P3      P4
------- ------- ------- -------
1       1       2       NULL
3       1       NULL    1