Freigeben über

Faire une concaténation sur un résultat d’une requête

  • Artikel

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

SELECT   CustomerID, dbo.GetMedalResult(CustomerID)

FROM Sales.Customer

order by CustomerID

retourne le tableau suivant

CustomerID

SalesOrderIDs

1

43860,44501,45283,46042

2

46976,47997,49054,50216,51728,57044,63198,69488

3

44124,44791,45568,46377,47439,48378,49538,50748,53616,59011,65310,71889

…

…

 

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.