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

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!

Publicités

Étiquettes : , , ,

3 Réponses to “SQL Server 2005 – Récursivité hiérarchique pour concaténation de chaîne”

  1. Philippe Says:

    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 Says:

    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 Says:

    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

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion /  Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion /  Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion /  Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion /  Changer )

w

Connexion à %s


%d blogueurs aiment cette page :