Понимание функции PIVOT в T-SQL

Ответ 1

A PIVOT используется для поворота данных из одного столбца в несколько столбцов.

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

create table temp
(
  id int,
  teamid int,
  userid int,
  elementid int,
  phaseid int,
  effort decimal(10, 5)
)

insert into temp values (1,1,1,3,5,6.74)
insert into temp values (2,1,1,3,6,8.25)
insert into temp values (3,1,1,4,1,2.23)
insert into temp values (4,1,1,4,5,6.8)
insert into temp values (5,1,1,4,6,1.5)

select elementid
  , [1] as phaseid1
  , [5] as phaseid5
  , [6] as phaseid6
from
(
  select elementid, phaseid, effort
  from temp
) x
pivot
(
  max(effort)
  for phaseid in([1], [5], [6])
)p

Вот SQL Demo с рабочей версией.

Это также можно сделать с помощью динамического PIVOT, где вы динамически создаете список столбцов и выполняете PIVOT.

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.phaseid) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT elementid, ' + @cols + ' from 
            (
                select elementid, phaseid, effort
                from temp
           ) x
            pivot 
            (
                 max(effort)
                for phaseid in (' + @cols + ')
            ) p '


execute(@query)

Результаты для обоих:

ELEMENTID   PHASEID1    PHASEID5    PHASEID6
3           Null        6.74        8.25
4           2.23        6.8         1.5

Ответ 2

Это простой базовый пример, любезно пройдя через это.

Примеры SQL SERVER - PIVOT и UNPIVOT

Пример из вышеприведенной ссылки для таблицы продуктов:

SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
 PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
ORDER BY PRODUCT

оказывает:

 PRODUCT FRED  KATE
 --------------------
 BEER     24    12
 MILK      3     1
 SODA   NULL     6
 VEG    NULL     5

Аналогичные примеры можно найти в сообщении блога Сводные таблицы в SQL Server. Простой пример

Ответ 3

Чтобы установить ошибку совместимости

используйте это перед использованием функции поворота

ALTER DATABASE [dbname] SET COMPATIBILITY_LEVEL = 100  

Ответ 4

    SELECT <non-pivoted column>,
    [first pivoted column] AS <column name>,
    [second pivoted column] AS <column name>,
    ...
    [last pivoted column] AS <column name>
FROM
    (<SELECT query that produces the data>)
    AS <alias for the source query>
PIVOT
(
    <aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
    IN ( [first pivoted column], [second pivoted column],
    ... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;

USE AdventureWorks2008R2 ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost 
FROM Production.Product
GROUP BY DaysToManufacture;

    DaysToManufacture          AverageCost
0                          5.0885
1                          223.88
2                          359.1082
4                          949.4105

    -- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, 
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost 
    FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;




Here is the result set.
Cost_Sorted_By_Production_Days    0         1         2           3       4       
AverageCost                       5.0885    223.88    359.1082    NULL    949.4105

Ответ 5

Я был новичком в этом, и я создал хороший пост об этом... Моя проблема заключалась в том, как правильно применять агрегацию, и вот мой пост: http://jaider.net/posts/1176-pivot-in-sql-server-correct-aggregated-results/

В решении @bluefeet важно отметить, что elementid является ключевым столбцом вашего "невидимого" Group By. Кроме того, вы можете заменить elementid или добавить больше столбцов, например userid.

Ответ 6

Я хочу добавить кое-что, о чем никто не упомянул.

Функция pivot прекрасно работает, когда источник имеет 3 столбца: один для aggregate, один для развёртывания в виде столбцов с for, а другой для разворота для row. В примере продукта это QTY, CUST, PRODUCT.

Однако, если у вас есть больше столбцов в источнике, он разбивает результаты на несколько строк вместо одной строки на сводку, основываясь на уникальных значениях на дополнительный столбец (как Group By сделал бы в простом запросе).

Посмотрите этот пример, я добавил столбец метки времени в исходную таблицу:

Теперь посмотрим, как это повлияет:

SELECT CUST, MILK

FROM Product
-- FROM (SELECT CUST, Product, QTY FROM PRODUCT) p
PIVOT (
    SUM(QTY) FOR PRODUCT IN (MILK)
) AS pvt

ORDER BY CUST

Чтобы это исправить, вы можете либо извлечь подзапрос в качестве источника, как это делали все выше - только с 3 столбцами (это не всегда будет работать для вашего сценария, представьте, если вам нужно поставить условие where для timestamp).

Второе решение заключается в использовании group by и повторном суммировании значений столбцов.

SELECT 
CUST, 
sum(MILK) t_MILK

FROM Product
PIVOT (
    SUM(QTY) FOR PRODUCT IN (MILK)
) AS pvt

GROUP BY CUST
ORDER BY CUST

GO

Ответ 7

Сводная таблица используется для преобразования одного из столбцов в вашем наборе данных из строк в столбцы (это обычно называется расширяющим столбцом). В приведенном вами примере это означает преобразование строк PhaseID в набор столбцов, где для каждого отдельного значения, которое может содержать PhaseID, имеется один столбец - в данном случае 1, 5 и 6.

Эти поворотные значения сгруппированы по столбцу ElementID в приведенном вами примере.

Обычно вам также необходимо предоставить некоторую форму агрегации, которая дает вам значения, на которые ссылается пересечение значения разбрасывания (PhaseID) и значения группировки (ElementID). Хотя в приведенном примере агрегация, которая будет использоваться, неясна, но включает столбец Effort.

После этого поворота столбцы группировки и распределения используются для поиска значения агрегации. Или, в вашем случае, поиск ElementID и PhaseIDX Effort.

Используя терминологию группирования, распространения и агрегирования, вы обычно видите пример синтаксиса для сводной точки как:

WITH PivotData AS
(
    SELECT <grouping column>
        , <spreading column>
        , <aggregation column>
    FROM <source table>
)
SELECT <grouping column>, <distinct spreading values>
FROM PivotData
    PIVOT (<aggregation function>(<aggregation column>)
        FOR <spreading column> IN <distinct spreading values>));

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