SQLShack (Français)

Dans les quelques articles précédents de cette série, nous avons jeté les bases de la création dun rapport. Nous allons aller plus loin et voir comment utiliser lopérateur de table PIVOT SQL Server. Nous allons commencer par la simple requête et progresser lentement vers le SQL dynamique et PIVOT. Commençons.

Modèle de données et idée générale

Le modèle de données que nous utiliserons est le même que celui que nous avons utilisé tout au long de cette série.

Notre tâche aujourdhui est de créer un rapport (en utilisant lopérateur PIVOT SQL Server) où chaque ville sera sur une ligne séparée , et où nous compterons le nombre de tous les résultats dappels liés à chaque ville. Par conséquent, tous les résultats (toutes les valeurs du dictionnaire) doivent être des colonnes dans notre rapport.

Puisque « une image vaut mille mots », nous décrirons notre objectif final avec limage également.

Pour ce faire, nous devrons utiliser les données de 4 tables call, call_outcome, customer et city. Par conséquent, il Ce serait bien que vous examiniez de plus près le modèle ci-dessous et que vous analysiez comment ces tables sont liées.

Nous allons commencer par examiner les données des tables et, à chaque requête, nous rapprocher de le résultat souhaité (requête). Lapproche utilisée ici est la même que celle utilisée dans Learn SQL: Créer un rapport manuellement à laide de larticle sur les requêtes SQL.

Catégories et données de rapport

Le Le premier ensemble de requêtes avec lequel nous allons commencer est celui qui examine les données actuellement présentes dans les 4 tables que nous devrons utiliser pour créer le rapport. Nous allons le faire afin que nous puissions confirmer que le rapport final a renvoyé ce quil devrait. Après cela, nous créerons des requêtes wh ich renvoie les catégories de rapport et les données du rapport, ainsi que les requêtes de table SQL Server PIVOT.

Ces requêtes sont aussi simples quelles pourraient lêtre, il ny a donc rien de spécial à commenter concernant leur syntaxe. En ce qui concerne les données renvoyées, nous devons être conscients de ce qui suit:

• Le tableau des villes contient 6 villes, et nous devrions donc avoir 6 lignes dans le rapport final
• Il y a 3 résultats possibles dans le tableau call_outcome, nous devrions donc avoir 3 colonnes pour les résultats (4 colonnes au total – > une pour le nom de la ville)
• Lappel du tableau a 10 lignes (seulement 8 sur limage ci-dessus), donc dans le tableau final, la somme de tous les résultats devrait être de 10 (chaque appel peut avoir exactement 1 résultat)

La prochaine chose à faire consiste à préparer des catégories de rapports. Nous voulons avoir une combinaison de toutes les villes et de tous les résultats dappels possibles. Pour ce faire, nous utiliserons le CROSS JOIN (produit cartésien).

Avoir toutes les catégories de rapport garantira que nous aurons une ligne dans le rapport, peu importe si cette paire contient des données ou non. Et cest ce que nous voulons – voir 0 dans cette catégorie de rapport, et ne pas manquer complètement cette catégorie.

La prochaine chose à faire est de joindre les 4 tableaux contenant les données dont nous avons besoin.

La requête elle-même nest pas complexe. Dans le résultat, nous devrions remarquer que nous avons 10 lignes, le même nombre que le nombre dappels que nous avons dans la base de données. De plus, j’ai utilisé la fonction DATEDIFF pour renvoyer la durée de chaque appel en secondes. Je pourrais simplement mettre call.id ici, mais je voulais nous rappeler la fonction DATEDIFF que nous avons mentionnée dans cet article. Nous pourrions utiliser cette durée si nous avons besoin dune SOMME / MOYENNE de secondes passées pour chaque catégorie de rapport (résultat de lappel de la ville &).

Rapport sans SQL Server PIVOT TABLE

Nous sommes maintenant prêts à rejoindre des catégories et des données. Nous utiliserons les deux requêtes mentionnées précédemment comme sous-requêtes et les joindrons en utilisant LEFT JOIN (afin davoir toutes les catégories présentes dans la sortie finale).

Nous avons les 18 combinaisons (6 villes différentes * 3 résultats dappels différents), et nous avons également les 10 appels présents ici (lignes avec la durée de lappel < > NULL).

Créons maintenant la requête PIVOT SQL Server.

TABLEAU PIVOT SQL Server (statique)

Jusquà présent, nous avons réussi à extraire les données dont nous avons besoin, et nous les avons comme liste. Nous pourrions exporter des données vers Excel et y effectuer des transformations. Nous allons le faire dans le prochain article.Pourtant, aujourdhui, nous voulons résoudre ce problème à laide de lopérateur PIVOT SQL Server. Dans cette section, nous aborderons le PIVOT SQL Server « statique ». Examinons dabord la requête utilisée et le résultat.

Nous pouvons remarquer que le résultat est exactement ce que nous voulions. Nous avons chaque ville sur une ligne et les 3 catégories pour les résultats des appels dans des colonnes séparées. Sil ny a pas de données pour une certaine paire ville-résultat, cette cellule doit contenir la valeur 0.

Commentons maintenant la requête SQL Server PIVOT. Voici quelques éléments que je trouve important de mentionner ici:

• Dans la première partie de la requête – report_data, nous avons copié-collé la requête de la section précédente. Le seul changement était que nous navions pas ORDER BY et GROUP BY dans cette partie de la requête car ils ne peuvent pas être appliqués ici (ils devraient aller après le résultat final de la requête est généré)
• La partie PIVOT de la requête se compose de 2 parties. Dans la première, nous définirons la fonction dagrégation à appliquer. Dans notre cas, il sagit de – COUNT ( call_duration). Dans la partie FOR de la qu ery, nous allons définir des colonnes. Nous listons littéralement toutes les valeurs que nous voulons avoir sous forme de colonnes. Cest un codage en dur. Si nous ajoutons une nouvelle valeur à la table, cela naura pas dimpact sur la requête (et cela devrait)

Cette requête fait son travail, et elle fonctionnera parfaitement si nous ne changeons jamais (ajoutez , supprimer, mettre à jour leurs noms) les résultats des appels dans la table associée. Cela pourrait savérer être le problème dans les cas où nous devons apporter des modifications au dictionnaire. Nous ne voulons pas nous demander sil existe une requête qui ne fonctionne pas comme prévu. Pour résoudre ce problème, nous devrons utiliser un nouveau concept – SQL dynamique.

TABLEAU PIVOT DE Dynamic SQL Server

Avant de passer au code, expliquons brièvement ce que le SQL dynamique est (nous en donnerons un examen beaucoup plus détaillé dans les prochains articles). Lexplication la plus simple est quen SQL, vous pouvez «créer» vos requêtes sous forme de chaînes, puis transmettre cette chaîne en tant que paramètre pour la procédure stockée SQL Server. Cette procédure exécutera la ou les instructions SQL stockées dans cette chaîne (bien sûr , si la syntaxe est correcte).

Bien que cela puisse sembler quelque chose de moins utilisé, il y a de nombreux endroits où cela vous facilite la vie. Création dune requête de table SQL Server PIVOT avec une inconnue nombre de colonnes est exactement un tel cas.

Lidée de cette approche est la suivante:

• Nous déclarerons une variable où stocker tous les noms de colonne (@columns ), et la variable où stocker la requête complète (@query)
• À laide de la requête SELECT, nous trouverons toutes les valeurs stockées dans la colonne result_text. Le code suivant – @columns + = QUOTENAME (TRIM (co.outcome_text)) + ,, ajoutera le nom de colonne à la liste de tous les noms de colonne précédents renvoyés par la requête. Le résultat est que nous avons tous les noms de colonne stockés dans th La variable @columns
• Dans la variable @query, nous allons stocker la requête complète de la section précédente, sauf pour la partie où les colonnes sont définies. Nous obtiendrons cette partie de la variable @columns
• La dernière chose que nous devons faire est de transmettre la requête complète en tant que paramètre à la procédure système SQL Server sp_executesql

Le résultat final est le même que dans la section précédente, mais cette fois nous sommes sûrs que notre requête fonctionnera même si nous apportons des modifications à les valeurs result.outcome_text. En outre, vous pouvez facilement modifier cette requête et calculer d’autres valeurs, par exemple, la durée d’appel SUM / AVG par paire ville-résultat.

Conclusion

L’opérateur PIVOT SQL Server vous donne un une vue complètement nouvelle de ce que vous pouvez réaliser directement sur la couche de base de données. Lorsquil est combiné avec le SQL dynamique, cela va encore plus loin. Je vous encourage fortement à jouer avec – il ny a pas de meilleure façon dapprendre que de lessayer vous-même. Dans le prochain article, nous montrerons comment nous pourrions utiliser la sortie de requête SQL et créer des tableaux et des graphiques dans Excel (cela fonctionnera non seulement dans SQL Server mais en général).

Table des matières

Apprendre SQL: CREATE DATABASE & Opérations CREATE TABLE

Apprendre SQL: INSÉRER DANS LA TABLE

Apprendre SQL: clé primaire

Apprendre SQL: clé étrangère

Apprendre SQL: instruction SELECT

Apprendre SQL: INNER JOIN vs LEFT JOIN

Apprendre SQL: Scripts SQL

Apprendre SQL: Types de relations

Apprendre SQL: Join plusieurs tables

Apprendre SQL: fonctions dagrégation

Apprendre SQL: Comment écrire une requête SELECT complexe?

Apprendre SQL: la base de données INFORMATION_SCHEMA

Apprendre SQL: Types de données SQL

Apprendre SQL: Théorie des ensembles

Apprendre SQL: fonctions définies par lutilisateur

Apprendre SQL: procédures stockées définies par lutilisateur

Apprendre SQL: vues SQL

Apprendre SQL: déclencheurs SQL

Apprendre SQL: sexercer aux requêtes SQL

Apprendre SQL: exemples de requêtes SQL

Apprendre SQL: créer un rapport manuellement à laide de requêtes SQL

Apprendre SQL: fonctions de date et dheure SQL Server

Apprendre SQL: créer des rapports SQL Server utilisation des fonctions de date et dheure

Apprenez SQL: tableaux croisés dynamiques SQL Server

Apprenez SQL: exportation SQL Server vers Excel

Apprendre SQL: introduction aux boucles SQL Server

Apprendre SQL: curseurs SQL Server

Apprendre SQL: bonnes pratiques SQL pour la suppression et la mise à jour de données

Apprendre SQL: conventions de dénomination

Apprendre SQL: tâches liées à SQL

Apprendre SQL: jointures non équi dans SQL Server

Apprendre SQL: injection SQL

• Auteur
• Messages récents

Emil est un professionnel des bases de données avec plus de 10 ans dexpérience dans tout ce qui concerne les bases de données. Au cours des années, il a travaillé dans lindustrie informatique et financière et travaille maintenant comme pigiste.
Ses engagements passés et présents vont de la conception et du codage de bases de données à lenseignement, au conseil et à la rédaction de bases de données. A ne pas oublier aussi, BI, création dalgorithmes, échecs, philatélie, 2 chiens, 2 chats, 1 femme, 1 bébé …
Vous pouvez le trouver sur LinkedIn
Voir tous les articles dEmil Drkusic

Derniers articles dEmil Drkusic (tout voir)

• Learn SQL: SQL Injection – 2 novembre 2020
• Learn SQL: Non-Equi Joins in SQL Server – 29 septembre 2020
• Learn SQL: Emplois liés à SQL – 1er septembre 2020