Имеет ли T-SQL агрегатную функцию для объединения строк? [дубликат]

Возможные Дубликаты:
функция типа Implode в SQL Server 2000?
объединить значения строк T-SQL

у меня есть представление, которое я запрашиваю, что выглядит так:

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

мне нужно написать запрос, который группирует BuildingNames вместе и отображает список PollNumbers следующим образом:

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

как я могу сделать это в T-SQL? Я бы предпочел не прибегать к написанию сохраненного процедура для этого, так как это кажется излишним, но я не совсем человек базы данных. Похоже, что агрегатная функция, такая как SUM() или AVG (),-это то, что мне нужно, но я не знаю, есть ли у T-SQL. Я использую SQL Server 2005.

2 67
sql-server tsql sql-server-2005 aggregate-functions group-by

2 ответа:

для SQL Server 2017 и выше используйте:

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

выход:

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

(3 rows affected)

для SQL Server 2005 и до 2016 года, вам нужно сделать что-то вроде этого:

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

выход:

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

(3 row(s) affected)

кроме того, будьте осторожны, не все FOR XML PATH конкатенации будут правильно обрабатывать специальные символы XML, как в моем примере выше.

103

в Sql Server нет встроенной функции, но это может быть достигнуто путем написания пользовательского агрегата. В этой статье упоминается такая функция как часть примеров SQL Server:http://msdn.microsoft.com/en-us/library/ms182741.aspx

в качестве примера я включаю код для объединения агрегата. Чтобы использовать его, создайте проект базы данных в Visual Studio, добавьте новый SqlAggregate и замените код примером ниже. После развертывания вы должны найти новый сборка в вашей базе данных и агрегатная функция 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());
    }
}

чтобы использовать его, вы можете просто написать агрегатный запрос:

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

вывод запроса:

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

я упаковал класс и агрегат в виде скрипта, который вы можете найти здесь:https://gist.github.com/FilipDeVos/5b7b4addea1812067b09

36