¿T-SQL tiene una función agregada para concatenar cadenas? [duplicar]
Posibles duplicados:
¿Función de tipo implosión en SQL Server 2000?
Concatenar valores de fila T-SQL
Tengo una vista que estoy consultando y que se parece a esta:
BuildingName PollNumber ------------ ---------- Foo Centre 12 Foo Centre 13 Foo Centre 14 Bar Hall 15 Bar Hall 16 Baz School 17
Necesito escribir una consulta que agrupe los nombres de los edificios y muestre una lista de números de encuesta como esta:
BuildingName PollNumbers ------------ ----------- Foo Centre 12, 13, 14 Bar Hall 15, 16 Baz School 17
¿Cómo puedo hacer esto en T-SQL? Preferiría no recurrir a escribir un procedimiento almacenado para esto, ya que parece excesivo, pero no soy exactamente una persona de bases de datos. Parece que lo que necesito es una función agregada como SUM() o AVG(), pero no sé si T-SQL tiene una. Estoy usando SQL Server 2005.
para SQL Server 2017 y versiones posteriores:
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
PRODUCCIÓN:
HeaderValue
----------- -------------
1 CCC
2 B<&>B, AAA
3 <br>, A & Z
(3 rows affected)
para SQL Server 2005 y hasta 2016, debe hacer algo como esto:
--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
PRODUCCIÓN:
HeaderValue ChildValues
----------- -------------------
1 CCC
2 AAA, B<&>B
3 <br>, A & Z
(3 row(s) affected)
Además, tenga cuidado, no todas FOR XML PATHlas concatenaciones manejarán adecuadamente los caracteres especiales XML como lo hará mi ejemplo anterior.
No existe una función integrada en Sql Server, pero se puede lograr escribiendo un agregado definido por el usuario. Este artículo menciona dicha función como parte de los ejemplos de SQL Server: http://msdn.microsoft.com/en-us/library/ms182741.aspx
Como ejemplo incluyo el código para un agregado Concatenar. Para usarlo, cree un proyecto de base de datos en Visual Studio, agregue el nuevo SqlAggregate y reemplace el código con el siguiente ejemplo. Una vez implementado, debería encontrar un nuevo ensamblado en su base de datos y una función agregada.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());
}
}
Para usarlo, simplemente puede escribir una consulta agregada:
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
El resultado de la consulta es:
-- Output
-- ===================
-- This is just a test
-- ,
-- do not be alarmed , this is just a test
Empaqueté la clase y el agregado como un script que puedes encontrar aquí: https://gist.github.com/FilipDeVos/5b7b4addea1812067b09
