¿T-SQL tiene una función aggregate para concatenar cadenas? [duplicar]
Posibles Duplicados:
Implode tipo función en SQL Server 2000?
Concatenar valores de fila T-SQL
Tengo una opinión que estoy preguntando que se ve así:{[12]]}
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 BuildingNames 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? Prefiero no recurrir a escribir un procedimiento almacenado para esto, ya que parece exagerado, pero no soy exactamente una persona de base de datos. Parece que una función agregada como SUM () o AVG () es lo que necesito, pero no se si T-SQL tiene una. Estoy usando SQL Server 2005.
2 answers
Para SQL Server 2017 y posteriores use:
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
SALIDA:
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
SALIDA:
HeaderValue ChildValues
----------- -------------------
1 CCC
2 AAA, B<&>B
3 <br>, A & Z
(3 row(s) affected)
También, cuidado, no todas las concatenaciones FOR XML PATH manejarán correctamente los caracteres especiales XML como lo hará mi ejemplo anterior.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-08-23 13:08:55
No hay una función incorporada 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 Concatenado. Para usarlo, cree un proyecto de base de datos en Visual Studio, agregue un nuevo SqlAggregate y reemplace el código con el ejemplo a continuación. Una vez implementado, debe 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
La salida 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-09-30 18:14:02