webentwicklung-frage-antwort-db.com.de

Verfügt T-SQL über eine Aggregatfunktion zum Verketten von Zeichenfolgen?

Mögliche Duplikate:
Implodiertypfunktion in SQL Server 2000?
Zeilenwerte verketten T-SQL

Ich habe eine Ansicht, die ich abfrage, die so aussieht:

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

Ich muss eine Abfrage schreiben, die BuildingNames gruppiert und eine Liste von PollNumbers wie folgt anzeigt:

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

Wie kann ich das in T-SQL machen? Ich würde lieber nicht darauf zurückgreifen, eine gespeicherte Prozedur dafür zu schreiben, da es wie ein Overkill aussieht, aber ich bin nicht gerade eine Datenbankperson. Es scheint, als ob eine Aggregatfunktion wie SUM () oder AVG () das ist, was ich brauche, aber ich weiß nicht, ob T-SQL eine hat. Ich verwende SQL Server 2005.

71
Brant Bobby

für SQL Server 2017 und höher:

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

AUSGABE:

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

(3 rows affected)

Für SQL Server 2005 und bis 2016 müssen Sie Folgendes tun:

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

AUSGABE:

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

(3 row(s) affected)

Auch aufpassen, nicht alle FOR XML PATH Verkettungen werden XML-Sonderzeichen wie in meinem obigen Beispiel korrekt verarbeiten.

110
KM.

Es gibt keine eingebaute Funktion in SQL Server, aber es kann durch Schreiben eines benutzerdefinierten Aggregats erreicht werden. Dieser Artikel erwähnt eine solche Funktion als Teil der SQL Server-Beispiele: http://msdn.Microsoft.com/en-us/library/ms182741.aspx

Als Beispiel füge ich den Code für ein verkettetes Aggregat ein. Erstellen Sie dazu ein Datenbankprojekt in Visual Studio, fügen Sie neues SqlAggregate hinzu, und ersetzen Sie den Code durch das folgende Beispiel. Nach der Bereitstellung sollten Sie eine neue Assembly in Ihrer Datenbank und eine Aggregatfunktion Concatenate finden.

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());
    }
}

Um es zu verwenden, können Sie einfach eine aggregierte Abfrage schreiben:

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

Die Ausgabe der Abfrage ist:

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

Ich habe die Klasse und das Aggregat als Skript zusammengefasst, das Sie hier finden: https://Gist.github.com/FilipDeVos/5b7b4addea1812067b09

36
Filip De Vos