Comprendre la fonction PIVOT en T-SQL

Question

Je suis très novice en matière de SQL.

J'ai une table comme celle-ci :

ID | TeamID | UserID | ElementID | PhaseID | Effort
-----------------------------------------------------
1  |   1    |  1      |   3       |  5     |   6.74
2  |   1    |  1      |   3       |  6     |   8.25
3  |   1    |  1      |   4       |  1     |   2.23
4  |   1    |  1      |   4       |  5     |   6.8
5  |   1    |  1      |   4       |  6     |   1.5

Et on m'a dit d'obtenir des données comme celles-ci

ElementID | PhaseID1 | PhaseID5 | PhaseID6
--------------------------------------------
    3     |   NULL   |   6.74   |   8.25
    4     |   2.23   |   6.8    |   1.5

Je comprends que je dois utiliser la fonction PIVOT. Mais je n'arrive pas à le comprendre clairement. Ce serait une grande aide si quelqu'un pouvait l'expliquer dans le cas ci-dessus (ou toute autre alternative, le cas échéant).

Answer 1

A PIVOT utilisé pour faire pivoter les données d'une colonne vers plusieurs colonnes.

Pour votre exemple, il s'agit d'un pivot STATIQUE, ce qui signifie que vous devez coder en dur les colonnes que vous souhaitez faire pivoter :

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

Voici un Démonstration SQL avec une version fonctionnelle.

Cela peut également se faire par le biais d'un PIVOT dynamique où vous créez la liste des colonnes de façon dynamique et effectuez le 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)

Les résultats pour les deux :

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

Answer 2

Ce sont les exemples de pivots les plus basiques, passez-les gentiment en revue.

SQL SERVER - Exemples de tables PIVOT et UNPIVOT

Exemple du lien ci-dessus pour le tableau des produits :

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

rend :

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

Vous trouverez des exemples similaires dans l'article de blog Tableaux croisés dynamiques dans SQL Server. Un exemple simple

Answer 3

J'ai quelque chose à ajouter ici que personne n'a mentionné.

En pivot fonctionne très bien lorsque la source comporte 3 colonnes : Une pour le aggregate une à étaler en colonnes avec for et un autre comme pivot pour row distribution. Dans l'exemple du produit, il s'agit de QTY, CUST, PRODUCT .

Cependant, si vous avez plus de colonnes dans la source, les résultats seront répartis en plusieurs lignes au lieu d'une ligne par pivot basée sur des valeurs uniques par colonne supplémentaire (comme dans le cas de Group By dans une simple requête).

Dans cet exemple, nous avons ajouté une colonne d'horodatage à la table source :

Maintenant, voyez son impact :

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

---

Pour résoudre ce problème, vous pouvez soit tirer une sous-requête comme source, comme tout le monde l'a fait ci-dessus, avec seulement 3 colonnes (cela ne va pas toujours fonctionner pour votre scénario, imaginez si vous avez besoin de mettre une where condition pour l'horodatage).

La deuxième solution consiste à utiliser un group by et refaire la somme des valeurs des colonnes pivotées.

SELECT 
CUST, 
sum(MILK) t_MILK

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

GROUP BY CUST
ORDER BY CUST

GO

Answer 4

Un pivot est utilisé pour convertir l'une des colonnes de votre ensemble de données de lignes en colonnes (ce qui est généralement appelé le colonne d'étalement ). Dans l'exemple que vous avez donné, cela signifie qu'il faut convertir l'indicateur PhaseID en un ensemble de colonnes, où il y a une colonne pour chaque valeur distincte que l'on peut obtenir. PhaseID peut contenir - 1, 5 et 6 dans ce cas.

Ces valeurs pivotées sont groupé via le ElementID dans l'exemple que vous avez donné.

En règle générale, vous devez également fournir une forme de agrégation qui vous donne les valeurs référencées par l'intersection du valeur de diffusion ( PhaseID ) et le valeur de regroupement ( ElementID ). Bien que dans l'exemple donné, le agrégation qui sera utilisé n'est pas clair, mais implique le Effort colonne.

Une fois ce pivotement effectué, le regroupement y colonnes d'épandage sont utilisés pour trouver un valeur de regroupement . Ou dans votre cas, ElementID y PhaseIDX recherche Effort .

Utilisation de la regroupement, diffusion, agrégation Dans la terminologie, vous verrez généralement un exemple de syntaxe pour un pivot comme :

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>));

Ce site donne une explication graphique de la façon dont le le regroupement, la diffusion et l'agrégation des colonnes converties à partir de la source en tableaux croisés dynamiques si cela peut aider davantage.

Answer 5

    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