Premier cas : Concaténer le résultat d'une colonne sur une ligne

Le principe est d'empiler les résultats d'un SELECT dans une variable.

Prenons un exemple sur la table HumanResources.Department de la base exemple de SQL Server 2005, AdventureWorks.

Concaténons le nom des départements.

USE AdventureWorks

DECLARE @DptName varchar(max);

SET @DptName = '';

select @DptName= @DptName + COALESCE(Name +',','') from HumanResources.Department;

IF @DptName IS NOT NULL

SET @DptName = SUBSTRING(@DptName,1,LEN(@DptName)-1)

SELECT @DptName

Cette méthode possède l'avantage de travailler de manière ensembliste et ne fait pas appel à un curseur.

Deuxième cas : Concaténer les lignes jointes

Traitons ce cas de trois manières et comparons le temps CPU à l'aide de l'instruction set statistics time ON.

Le premier cas de figure utilise une fonction explicitement crée

CREATE FUNCTION dbo.GetMedalResult

(

    @CustomerID bigint

)

RETURNS VARCHAR(max)

AS

BEGIN

    DECLARE @r VARCHAR(max)

      SET  @r = ''

    SELECT @r = @r + COALESCE(convert(varchar(max),SalesOrderID )+',', '')        

        FROM Sales.SalesOrderHeader

        WHERE CustomerID = @CustomerID

      IF @r <> ''

      SET @r = SUBSTRING(@r,1,LEN(@r)-1)

    RETURN @r

END

GO

La requête

retourne le tableau suivant

Temps CPU :

Sur mon environnement de travail, j'obtiens les résultats suivant :

CPU time = 3588 ms, elapsed time = 3702 ms.

Le deuxième cas de figure s'appuie sur les fonctionnalités de XML incluses dans le moteur SQL Server.

USE AdventureWorks

GO

SELECT

    CustomerID,

    SalesOrderIDs = REPLACE(

        (

            SELECT

                SalesOrderID AS [data()]

            FROM

                Sales.SalesOrderHeader soh

            WHERE

                soh.CustomerID = c.CustomerID

            ORDER BY

                SalesOrderID

            FOR XML PATH ('')

        ), ' ', ',')

FROM

    Sales.Customer c

ORDER BY

    CustomerID

Temps CPU :

--CPU time = 265 ms, elapsed time = 378 ms.

Le temps CPU est divisé par plus de 10 en utilisant la les fonctionnalités de XML incluses dans le moteur SQL Server. La fonction data renvoie les éléments sur une même ligne séparée d'un espace.

Le troisième cas de figure s'appuie sur en plus des XML de la nouvelle clause SQL Server 2005 CROSS APPLY

USE AdventureWorks

GO

SELECT

    CustomerID,

    SalesOrderIDs = LEFT(o.list, LEN(o.list)-1)

FROM

    Sales.Customer c

CROSS APPLY

(

        SELECT

            CONVERT(VARCHAR(12), SalesOrderID) + ',' AS [text()]

        FROM

            Sales.SalesOrderHeader s

        WHERE

            s.CustomerID = c.CustomerID

        ORDER BY

            SalesOrderID

        FOR XML PATH('')

    ) o (list)

ORDER BY

    CustomerID

Temps CPU :

CPU time = 218 ms, elapsed time = 330 ms.

Le temps CPU est légèrement plus performant que le précédant cas de figure.