SQL Server 2005 – Récursivité hiérarchique pour concaténation de chaîne

By mytips

Tip SQL Server 2005

Concaténation de chaînes d’une base de donnée hiérarchique

Article du 21 septembre 2007

Récursivité à l’aide des CTE de SQL Server 2005

Ce billet explique comment faire pour récupérer une chaine de caractère qui est la concaténation de toutes les valeurs d’un champs dans les différents niveaux d’une base de donnée hierarchique (en SQL Server 2005).

Un exemple pour montrer à quel résultat vous arriverez, exemple d’une base de donnée des états, pays, zones géographique :
Structure de la table :

CREATE TABLE [dbo].[T_LOCATION] (
[ID_LOCATION] [int] IDENTITY (1, 1) NOT NULL ,
[LOCATION_NAME] [varchar] (255) NOT NULL ,
[IDR_PARENT_LOCATION] [int],
CONSTRAINT PK_LOCATION PRIMARY KEY CLUSTERED(ID_LOCATION)
)

Nous avons les enregistrements suivants :

ID_LOCATION LOCATION_NAME IDR_PARENT_LOCATION
1 Europe NULL
2 France 1
3 Espagne 1
4 Amérique du nord NULL
5 Canada 4
6 USA 4
7 Utah 6
8 Texas 6

Je veux qu’en appellant ma fonction de concaténation avec comme argument l’identifiant d’un lieu, il me renvoit le nom de ce lieu et de ses « parents », concaténés.

Exemple :

Select getLocationHierarchy(8)
Resultat: "Texas USA Amérique du Nord"

Select getLocationHierarchy(3)
Resultat: "Espagne Europe"

Pour cela on utilise une nouveauté dans SQL Server 2005, les Common Table Expressions (CTE).

Bon voilà comment j’ai fait pour avoir le résultat ci-dessus, un exemple valant mieux qu’un long discours!


CREATE FUNCTION getLocationHierarchy (@id as INT)
RETURNS VARCHAR(3000) WITH SCHEMABINDING AS
BEGIN
DECLARE @hierarchy VARCHAR(3000)
SET @hierarchy = ''; /* j'aime bien initialiser les variables... */
-- Declaration de la CTE
with temp as
(
-- On récupère le premier enregistrement, celui qui correspond à l'ID passé
SELECT
CAST (LOCATION_NAME as VARCHAR(3000)) AS LOCATION_NAME,IDR_PARENT_LOCATION,ID_LOCATION FROM dbo.T_LOCATION WHERE ID_LOCATION = @id
-- On va chercher les parents et on concatène les noms
UNION ALL
SELECT CAST ((t.LOCATION_NAME+ ' ' +t1.LOCATION_NAME) AS VARCHAR(3000)) AS LOCATION_NAME ,t1.IDR_PARENT_LOCATION,t1.ID_LOCATION
FROM dbo.T_LOCATION t1 INNER JOIN TEMP t ON t.IDR_PARENT_LOCATION = t1.ID_LOCATION)
-- récupération de la chaine concaténée
SELECT @hierarchy = LOCATION_NAME FROM temp WHERE IDR_PARENT_LOCATION IS NULL
RETURN (@hierarchy)
END

Voilà, si vous avez des commentaires, n’hésitez pas!

Mots-clefs : , , ,

Cette entrée a été publiée le septembre 28, 2007 à 9:17 et est en lien avec BDD, sql server. Vous pouvez suivre toutes les réponses à cet article à l'aide du flux RSS 2.0. Vous pouvez laisser une réponse, ou un trackback depuis votre site.

3 réponses vers «SQL Server 2005 – Récursivité hiérarchique pour concaténation de chaîne»

  1. Philippe dit :
    juillet 21, 2008 à 11:27

    Bravo pour cette fonction trés interssante, je suis un système de gestion documentaire qui présente la meme organisation parent-enfants. Par contre, j’ai un problème avec la fonction, voici le message d’erreur:

    Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

  2. mytips dit :
    juillet 21, 2008 à 4:25

    J’ai ajouté les commentaires après avoir créé la fonction, enlevez les commentaires, cela devrait marcher :

    CREATE FUNCTION getLocationHierarchy (@id as INT)
    RETURNS VARCHAR(3000) WITH SCHEMABINDING AS
    BEGIN
    DECLARE @hierarchy VARCHAR(3000)
    SET @hierarchy =  »;

    with temp as
    (
    SELECT
    CAST (LOCATION_NAME as VARCHAR(3000)) AS LOCATION_NAME,IDR_PARENT_LOCATION,ID_LOCATION FROM dbo.T_LOCATION WHERE ID_LOCATION = @id
    UNION ALL
    SELECT CAST ((t.LOCATION_NAME+ ‘ ‘ +t1.LOCATION_NAME) AS VARCHAR(3000)) AS LOCATION_NAME ,t1.IDR_PARENT_LOCATION,t1.ID_LOCATION
    FROM dbo.T_LOCATION t1 INNER JOIN TEMP t ON t.IDR_PARENT_LOCATION = t1.ID_LOCATION)
    SELECT @hierarchy = LOCATION_NAME FROM temp WHERE IDR_PARENT_LOCATION IS NULL
    RETURN (@hierarchy)
    END

  3. mytips dit :
    juillet 21, 2008 à 4:26

    Il faudra remplacer les quotes penchées due à la font utilisée par wordpress par des quotes normales (caractère en dessous du nombre 4)

Laisser un commentaire