Czy T-SQL ma zagregowaną funkcję do łączenia łańcuchów? [duplikat]
Możliwe duplikaty:
Funkcja Typu Implode w SQL Server 2000?
Concatenate row values T-SQL
Mam widok, który odpytuję, który wygląda tak:
BuildingName PollNumber ------------ ---------- Foo Centre 12 Foo Centre 13 Foo Centre 14 Bar Hall 15 Bar Hall 16 Baz School 17
Muszę napisać zapytanie, które grupuje BuildingNames razem i wyświetla listę PollNumbers w ten sposób:
BuildingName PollNumbers ------------ ----------- Foo Centre 12, 13, 14 Bar Hall 15, 16 Baz School 17
Jak mogę to zrobić w T-SQL? Wolałbym nie uciekać się do pisania procedury składowanej w tym celu, ponieważ to wygląda na przesadę, ale nie jestem osobą z bazy danych. Wygląda na to, że funkcja agregująca, taka jak SUM () lub AVG (), jest tym, czego potrzebuję, ale nie wiem, czy T-SQL taką ma. Używam SQL Server 2005.
2 answers
Dla SQL Server 2017 i nowszych:
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
Wyjście:
HeaderValue
----------- -------------
1 CCC
2 B<&>B, AAA
3 <br>, A & Z
(3 rows affected)
Dla SQL Server 2005 i do 2016, musisz zrobić coś takiego:
--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
Wyjście:
HeaderValue ChildValues
----------- -------------------
1 CCC
2 AAA, B<&>B
3 <br>, A & Z
(3 row(s) affected)
Uważaj również, nie wszystkie FOR XML PATH konkatenacje będą poprawnie obsługiwać znaki specjalne XML, jak mój powyższy przykład.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-08-23 13:08:55
W Sql serverze nie ma wbudowanej funkcji, ale można ją osiągnąć, pisząc agregat zdefiniowany przez użytkownika. Ten artykuł wspomina o takiej funkcji jako części SQL Server samples: http://msdn.microsoft.com/en-us/library/ms182741.aspx
Jako przykład podaję kod agregatu Konkatenacyjnego. Aby go użyć, Utwórz projekt bazy danych w Visual Studio, Dodaj nowy SqlAggregate i zastąp kod poniższym przykładem. Po wdrożeniu powinieneś znaleźć nowy zespół w swoim baza danych i funkcja agregująca 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());
}
}
Aby go użyć, możesz po prostu napisać zbiorcze zapytanie:
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
Wyjście zapytania to:
-- Output
-- ===================
-- This is just a test
-- ,
-- do not be alarmed , this is just a test
Spakowałem klasę i agregat jako skrypt, który można znaleźć tutaj: 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/doraprojects.net/template/agent.layouts/content.php on line 54
2014-09-30 18:14:02