SQL Server : concatener des lignes avec STUFF

Sur un des projets sur lequel j’ai travaillé, un des besoins était de concaténer des informations présentes sur plusieurs lignes en une ligne avec un dénominateur commun.
Ce traitement était uniquement possible côté SQL, étant donné que le résultat de cette requête était directement affiché, sans traitement préalable possible. Sinon, dans la plupart des cas, j’aurai fait ce traitement dans le code dans la partie Business de l’application.

Exemple

Prenons les tables suivantes, l’une liste des départements, l’autre liste des villes dont chacune est liée à un département (relation 1-n)

SQLStuff01

 

Dans ma requête, je veux pouvoir concaténer sur une même ligne, l’ensemble des villes d’un département donné (le dénominateur commun).
Le résultat de la requête doit nous donner le résultat suivant :

SQLStuff02

 

Voici donc la requête à exécuter, avec la fonction STUFF :

SQLStuff03

 

Encore une fois, j’ai utilisé cette requête dans un cas d’affichage bien précis! A mes yeux ce n’est pas la meilleure des pratiques, c’est un traitement que l’on devrait pouvoir faire plutôt dans la partie code. Néanmoins on peut utiliser ce type de requête dans une View SQL et l’appeler directement par exemple dans un rapport SSRS…

Partagez:

[SQL Server] Utilisation des CTE

Récemment, j’ai été amené à de nouveau utiliser ce qu’on appelle des expressions CTE (Common Table Expression).
Ayant un peu perdu l’habitude de les utiliser sous SQL Server, je me suis dit qu’un article sur cette fonctionnalité ne ferait pas de mal 🙂

Présentation
Les expressions CTE, ou requêtes récursives, ont été introduites depuis la version 2005 de SQL Server, mais cette fonctionnalité existait déjà dans d’autres SGBD (Oracle notamment).
Une expression CTE se présente dans une requête sous la forme d’une table temporaire dont la durée de vie est celle de la requête. Cette expression CTE sera interrogée de façon récursive afin d’en sortir un SELECT de résultats.

On définit une CTE en ajoutant une clause WITH avant un SELECT, INSERT, UPDATE, DELETE ou MERGE. On peut également l’utiliser dans une vue avant un SELECT.
La clause peut inclure une ou plusieurs CTE, comme le montre la syntaxe suivante:

[WITH <common_table_expression> [,…]]
<common_table_expression>::=
cte_name [(column_name [,…])]
AS (cte_query)

Exemple d’application simple (sans récursivité)
Nous allons maintenant directement voir dans quels cas concrètement utiliser ces expression, puis les expliquer via un exemple.
Problèmatique : Différents commerciaux réalisent un chiffre d’affaires pour leurs ventes à certaines dates, ces commerciaux ont des Managers afin de les superviser. Nous désirons voir pour chacun des commerciaux leur CA individuel et aggregé par dates.

Représentons ce problème via des tables sous SQL Server. Nous avons une table « sales » qui contiendra les données chiffrées des ventes associées à un commercial, puis une table SalesPeople qui contiendra l’ensemble des commerciaux et managers.

On notera deux choses :
– La même table stocke les commerciaux et manager, la jointure est réflexive ;
– Un commercial peut faire plusieurs ventes.

Sales CTE Schéma

Continuer la lecture de [SQL Server] Utilisation des CTE

Partagez: