T-SQL dispose-t-il d'une fonction d'agrégation pour concaténer des chaînes de caractères ?

Question

Duplicatas possibles :
Fonction de type Implode dans SQL Server 2000 ?
Concaténation des valeurs de lignes T-SQL

J'ai une vue que j'interroge et qui ressemble à ceci :

BuildingName    PollNumber
------------    ----------
Foo Centre      12        
Foo Centre      13
Foo Centre      14
Bar Hall        15
Bar Hall        16
Baz School      17

J'ai besoin d'écrire une requête qui regroupe les noms de bâtiments et affiche une liste de numéros de scrutin comme ceci :

BuildingName    PollNumbers
------------    -----------
Foo Centre      12, 13, 14
Bar Hall        15, 16
Baz School      17

Comment puis-je faire cela en T-SQL ? Je préférerais ne pas avoir à écrire une procédure stockée pour cela, car cela semble exagéré, mais je ne suis pas vraiment un spécialiste des bases de données. Il semble qu'une fonction d'agrégation comme SUM() ou AVG() soit ce dont j'ai besoin, mais je ne sais pas si T-SQL en possède une. J'utilise SQL Server 2005.

Answer 1

pour l'utilisation de SQL Server 2017 et plus :

STRING_AGG()

set nocount on;
declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5))
insert into @YourTable VALUES (1,1,'CCC')
insert into @YourTable VALUES (2,2,'B<&>B')
insert into @YourTable VALUES (3,2,'AAA')
insert into @YourTable VALUES (4,3,'<br>')
insert into @YourTable VALUES (5,3,'A & Z')
set nocount off
SELECT
    t1.HeaderValue
        ,STUFF(
                   (SELECT
                        ', ' + t2.ChildValue
                        FROM @YourTable t2
                        WHERE t1.HeaderValue=t2.HeaderValue
                        ORDER BY t2.ChildValue
                        FOR XML PATH(''), TYPE
                   ).value('.','varchar(max)')
                   ,1,2, ''
              ) AS ChildValues
    FROM @YourTable t1
    GROUP BY t1.HeaderValue

SELECT
    HeaderValue, STRING_AGG(ChildValue,', ')
    FROM @YourTable
    GROUP BY HeaderValue

SORTIE :

HeaderValue 
----------- -------------
1           CCC
2           B<&>B, AAA
3           <br>, A & Z

(3 rows affected)

pour SQL Server 2005 et jusqu'à 2016, vous devez faire quelque chose comme ceci :

--Concatenation with FOR XML and eleminating control/encoded character expansion "& < >"
set nocount on;
declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5))
insert into @YourTable VALUES (1,1,'CCC')
insert into @YourTable VALUES (2,2,'B<&>B')
insert into @YourTable VALUES (3,2,'AAA')
insert into @YourTable VALUES (4,3,'<br>')
insert into @YourTable VALUES (5,3,'A & Z')
set nocount off
SELECT
    t1.HeaderValue
        ,STUFF(
                   (SELECT
                        ', ' + t2.ChildValue
                        FROM @YourTable t2
                        WHERE t1.HeaderValue=t2.HeaderValue
                        ORDER BY t2.ChildValue
                        FOR XML PATH(''), TYPE
                   ).value('.','varchar(max)')
                   ,1,2, ''
              ) AS ChildValues
    FROM @YourTable t1
    GROUP BY t1.HeaderValue

SORTIE :

HeaderValue ChildValues
----------- -------------------
1           CCC
2           AAA, B<&>B
3           <br>, A & Z

(3 row(s) affected)

Aussi, faites attention, tous les FOR XML PATH Les concaténations traiteront correctement les caractères spéciaux XML comme le fait mon exemple ci-dessus.

Answer 2

Il n'y a pas de fonction intégrée dans Sql Server, mais cela peut être réalisé en écrivant un agrégat défini par l'utilisateur. Cet article mentionne une telle fonction comme faisant partie des échantillons de SQL Server : http://msdn.microsoft.com/en-us/library/ms182741.aspx

À titre d'exemple, j'ai inclus le code d'un agrégat de type Concaténation. Pour l'utiliser, créez un projet de base de données dans Visual Studio, ajoutez un nouveau SqlAggregate et remplacez le code par l'exemple ci-dessous. Une fois déployé, vous devriez trouver un nouvel assembly dans votre base de données et une fonction d'agrégation Concatenate

using System;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined, IsInvariantToNulls = true, IsInvariantToDuplicates = false, IsInvariantToOrder = false, MaxByteSize = 8000, Name = "Concatenate")]
public class Concatenate : IBinarySerialize
{
    private StringBuilder _intermediateResult;

    internal string IntermediateResult {
        get
        {
            return _intermediateResult.ToString();
        } 
    }

    public void Init()
    {
        _intermediateResult = new StringBuilder();
    }

    public void Accumulate(SqlString value)
    {
        if (value.IsNull) return;
        _intermediateResult.Append(value.Value);
    }

    public void Merge(Concatenate other)
    {
        if (null == other)
            return;

        _intermediateResult.Append(other._intermediateResult);
    }

    public SqlString Terminate()
    {
        var output = string.Empty;

        if (_intermediateResult != null && _intermediateResult.Length > 0)
            output = _intermediateResult.ToString(0, _intermediateResult.Length - 1);

        return new SqlString(output);
    }

    public void Read(BinaryReader reader)
    {
        if (reader == null) 
            throw new ArgumentNullException("reader");

        _intermediateResult = new StringBuilder(reader.ReadString());
    }

    public void Write(BinaryWriter writer)
    {
        if (writer == null) 
            throw new ArgumentNullException("writer");

        writer.Write(_intermediateResult.ToString());
    }
}

Pour l'utiliser, il suffit d'écrire une requête agrégée :

create table test(
  id int identity(1,1) not null
    primary key
, class tinyint not null
, name nvarchar(120) not null )

insert into test values 
(1, N'This'),
(1, N'is'),
(1, N'just'),
(1, N'a'),
(1, N'test'),
(2, N','),
(3, N'do'),
(3, N'not'),
(3, N'be'),
(3, N'alarmed'),
(3, N','),
(3, N'this'),
(3, N'is'),
(3, N'just'),
(3, N'a'),
(3, N'test')

select dbo.Concatenate(name + ' ')
from test
group by class

drop table test

La sortie de la requête est :

-- Output
-- ===================
-- This is just a test
-- ,
-- do not be alarmed , this is just a test

J'ai empaqueté la classe et l'agrégat dans un script que vous pouvez trouver ici : https://gist.github.com/FilipDeVos/5b7b4addea1812067b09

Answer 3

Si je faisais cela en C#, j'utiliserais LINQ-to-Entities et une requête LINQ. C'est indépendant de la base de données et ce sera rapide (Microsoft jure que la plupart des T-SQL générés par les requêtes LINQ sont meilleurs que les requêtes écrites à la main dans les situations courantes).

Une fois que vous avez mis en place LINQ to Entities, la R&D devient beaucoup plus facile. Si vous voulez vous mettre rapidement à niveau, je vous recommande de suivre quelques vidéos tutorielles (Google "LINQ tutorial videos").