Relationnel stocker d'énormes quantités de données sous forme de tableaux. Ces tables peuvent avoir n'importe quel nombre de lignes et de colonnes. Mais que se passerait-il si vous deviez modifier les données de niveau ligne en données en colonnes? Eh bien, dans cet article sur SQL Pivot, je vais vous montrer comment vous pouvez convertir des lignes en colonne dans un serveur SQL.
Les sujets suivants seront traités dans cet article:
Qu'est-ce que PIVOT dans SQL?
PIVOT est utilisé pour faire pivoter la valeur de la table en convertissant les valeurs uniques d'une seule colonne en plusieurs colonnes. Il est utilisé pour faire pivoter les lignes en valeurs de colonne et exécute des agrégations si nécessaire sur les valeurs de colonne restantes.
UNPIVOT, par contre, est utilisé pour effectuer les opérations opposées. Ainsi, il est utilisé pour convertir les colonnes d'une table spécifique en valeurs de colonne.
Dans cet article, laissez-nous comprendre la syntaxe de SQL Pivot.
Syntaxe:
SELECT NonPivoted ColumnName, [First Pivoted ColumnName] AS ColumnName, [Second Pivoted ColumnName] AS ColumnName, [Third Pivoted ColumnName] AS ColumnName, ... [Last Pivoted ColumnName] AS ColumnName FROM (SELECT requête qui produit les données) AS [alias pour la requête initiale] PIVOT ([AggregationFunction] (ColumName) FOR [ColumnName de la colonne dont les valeurs deviendront des en-têtes de colonne] IN ([First Pivoted ColumnName], [Second Pivoted ColumnName], [Third Pivoted ColumnName] ... [last colonne pivotée])) AS [alias du tableau croisé dynamique]
Ici,vous pouvez également utiliser le Clause ORDER BY pour trier les valeurs dans l'ordre croissant ou décroissant. Maintenant que vous savez ce qu'est PIVOT en SQL et sa syntaxe de base, allons de l'avant et voyons comment l'utiliser.
Exemples
Pour votre meilleure compréhension, je considérerai le tableau suivant pour vous expliquer tous les exemples.
Table des fournisseurs:
ID du fournisseur | Jours de fabrication | Coût | N ° de client | PurchaseID |
un | 12 | 1230 | Onze | P1 |
2 | vingt et un | 1543 | 22 | P2 |
3 | 32 | 2. 3. 4. 5 | Onze | P3 |
4 | 14 | 8765 | 22 | P1 |
5 | 42 | 3452 | 33 | P3 |
6 | 31 | 5431 | 33 | P1 |
sept | 41 | 2342 | Onze | P2 |
8 | 54 | 3654 | 22 | P2 |
9 | 33 | 1234 | Onze | P3 |
dix | 56 | 6832 | 33 | P2 |
Écrivons une requête simple pour récupérer le coût moyen dépensé par chaque client.
SELECT CustomerID, AVG (Cost) AS AverageCostofCustomer FROM Suppliers GROUP BY CustomerID
Production:
N ° de client | AverageCostofCustomer |
Onze | 1787,75 |
22 | 4654 |
33 | 5238,33 |
Maintenant, disons que nous voulons faire pivoter le tableau ci-dessus. Ici, les valeurs de la colonne CustomerID deviendront les en-têtes de colonne.
- Créez un tableau croisé dynamique avec une ligne et trois colonnes SELECT 'AverageCostofCustomer' AS Cost_According_To_Customers, [11], [22], [33] FROM (SELECT CustomerID, Cost FROM Suppliers) AS SourceTable PIVOT (AVG (Cost) FOR CustomerID IN ( [11], [22], [33])) AS PivotTable
Production:
Cost_According_To_Customers | Onze | 22 | 33 |
AverageCostofCustomer | 1787,75 | 4654 | 5238,33 |
Remarque: Lorsque vous utilisez fonctions d'agrégation avec PIVOT, les valeurs nulles ne sont pas prises en compte lors du calcul d'une agrégation.
Eh bien, c'était un exemple de base, mais comprenons maintenant comment la clause PIVOT fonctionnait.
Fonctionnement de la clause PIVOT
Comme vous pouvez vous y référer ci-dessus, pour créer une TABLE PIVOT, vous devez suivre les étapes ci-dessous:
- Sélectionnez les colonnes à pivoter
- Ensuite, sélectionnez une table source.
- Appliquez l'opérateur PIVOT, puis utilisez les fonctions d'agrégation.
- Mentionnez les valeurs de pivot.
Sélectionnez les colonnes à pivoter
Dans un premier temps, nous devons spécifier les champs à inclure dans nos résultats. Dans notre exemple, j'ai considéré la colonne AverageCostofCustomer dans le tableau croisé dynamique. Ensuite, nous avons créé trois autres colonnes avec les en-têtes de colonne 11, 22 et 33. Exemple-
SELECT 'Coût moyen du client' AS Cost_According_To_Customers, [11], [22], [33]
Sélectionnez la table source
Ensuite, vous devez spécifier l'instruction SELECT qui renverra les données source pour le tableau croisé dynamique. Dans notre exemple, nous renvoyons l'ID client et le coût de la table Fournisseurs.
(SELECT CustomerID, Cost FROM Suppliers) AS SourceTable
Appliquez l'opérateur PIVOT, puis utilisez les fonctions d'agrégation
Ensuite, vous devez spécifier la fonction d'agrégation à utiliser lors de la création du tableau croisé dynamique. Dans notre exemple, j'ai utilisé la fonction AVG pour calculer le coût moyen.
PIVOT (AVG (coût)
Mentionner les valeurs de pivot
Enfin, vous devez mentionner les valeurs qui doivent être incluses dans le tableau croisé dynamique résultant. Ces valeurs seront utilisées comme en-têtes de colonne dans le tableau croisé dynamique.
FOR CustomerID IN ([11], [22], [33])) AS PivotTable
C’est ainsi que fonctionnent les opérateurs PIVOT. En poursuivant cet article sur SQL PIVOT, comprenons à quel point il est différent de SQL UNPIVOT.
SQL UNPIVOT
L'opérateur SQL UNPIVOT permet d'effectuer l'opération inverse de celle de PIVOT. Il est utilisé pour faire pivoter les données de colonne en données de niveau ligne. La syntaxe d'UNPIVOT est similaire à celle de PIVOT. La seule différence est que vous devez utiliser le ' UNPIVOT » .
Exemple:
Créons une table avec les colonnes SupplierID, AAA, BBB et CCC. Insérez également quelques valeurs.
CREATE TABLE sampletable (SupplierID int, AAA int, BBB int, CCC int) GO INSERT INTO sampletable VALUES (1,3,5,6) INSERT INTO sampletable VALUES (2,9,2,8) INSERT INTO sampletable VALUES (3, 8,1,7) ALLER
Production:
ID du fournisseur | AAA | BBB | CCC |
un | 3 | 5 | 6 |
2 | 9 | 2 | 8 |
3 | 8 | un | sept |
Maintenant, disons, nous voulons décompresser le tableau. Pour ce faire, vous pouvez vous référer au code suivant:
SELECT SupplierID, Clients, Produits FROM (SELECT SupplierD, AAA, BBB, CCC FROM sampletable) p UNPIVOT (Produits POUR les clients IN (AAA, BBB, CCC)) COMME exemple GO
ID du fournisseur | Les clients | Des produits |
un | AAA | 3 |
un | BBB | 5 |
un | CCC | 6 |
2 | AAA | 9 |
2 | BBB | 2 |
2 | CCC | 8 |
3 contrôleur de vue de modèle en java | AAA | 8 |
3 | BBB | un |
3 | CCC | sept |
C’est ainsi que vous pouvez utiliser SQL PIVOT et UNPIVOT. Avec cela, nous arrivons à la fin de cet article. J'espère que vous avez compris, comment utiliser SQL. Si vous souhaitez en savoir plus sur MySQL et apprenez à connaître cette base de données relationnelle open-source, puis consultez notre qui vient avec une formation en direct animée par un instructeur et une expérience de projet réelle. Cette formation vous aidera à comprendre MySQL en profondeur et vous aidera à maîtriser le sujet.
Vous avez une question pour nous? Veuillez le mentionner dans la section commentaires de cet article sur SQL Pivot et je reviendrai vers vous.