Jeśli możesz używać CLR w swoim środowisku, jest to specjalnie dostosowany przypadek agregacji zdefiniowanej przez użytkownika.
W szczególności jest to prawdopodobnie właściwy sposób, jeśli dane źródłowe nie są trywialnie duże i / lub musisz często wykonywać tego typu czynności w swojej aplikacji. Podejrzewam, że plan zapytań dla rozwiązania Aarona nie będzie dobrze skalowany wraz ze wzrostem wielkości wejściowej. (Próbowałem dodać indeks do tabeli tymczasowej, ale to nie pomogło).
To rozwiązanie, podobnie jak wiele innych rzeczy, stanowi kompromis:
- Polityka / polityka dotycząca nawet korzystania z integracji CLR w środowisku twojego lub twojego klienta.
- Funkcja CLR jest prawdopodobnie szybsza i lepiej się skaluje, biorąc pod uwagę prawdziwy zestaw danych.
- Funkcja CLR będzie nadawać się do ponownego użycia w innych zapytaniach i nie będziesz musiał duplikować (i debugować) złożonego podzapytania za każdym razem, gdy będziesz tego potrzebować.
- Prosty T-SQL jest prostszy niż pisanie zewnętrznego kodu i zarządzanie nim.
- Być może nie wiesz, jak programować w C # lub VB.
- itp.
EDYCJA: Cóż, poszedłem sprawdzić, czy to rzeczywiście było lepsze, i okazuje się, że wymaganie, aby komentarze były w określonej kolejności, nie jest obecnie możliwe do spełnienia przy użyciu funkcji agregującej. :(
Zobacz SqlUserDefinedAggregateAttribute.IsInvariantToOrder . Zasadniczo, co trzeba zrobić, to OVER(PARTITION BY customer_code ORDER BY row_num)jednak ORDER BYnie jest obsługiwana w OVERklauzuli podczas agregowania. Zakładam, że dodanie tej funkcji do programu SQL Server otwiera puszkę robaków, ponieważ to, co należałoby zmienić w planie wykonania, jest banalne. Wyżej wspomniany link mówi, że jest on zarezerwowany do wykorzystania w przyszłości, więc można go zaimplementować w przyszłości (w 2005 r. Prawdopodobnie nie masz szczęścia).
Można to nadal osiągnąć poprzez spakowanie i parsowanie row_numwartości w zagregowanym ciągu, a następnie wykonanie sortowania w obiekcie CLR ... co wydaje się dość hackerskie.
W każdym razie poniżej znajduje się kod, którego użyłem na wypadek, gdyby ktokolwiek uznał to za przydatne, nawet z ograniczeniami. Część hakerską zostawię jako ćwiczenie dla czytelnika. Zauważ, że użyłem AdventureWorks (2005) do danych testowych.
Montaż agregatu:
using System;
using System.IO;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
namespace MyCompany.SqlServer
{
[Serializable]
[SqlUserDefinedAggregate
(
Format.UserDefined,
IsNullIfEmpty = false,
IsInvariantToDuplicates = false,
IsInvariantToNulls = true,
IsInvariantToOrder = false,
MaxByteSize = -1
)]
public class StringConcatAggregate : IBinarySerialize
{
private string _accum;
private bool _isEmpty;
public void Init()
{
_accum = string.Empty;
_isEmpty = true;
}
public void Accumulate(SqlString value)
{
if (!value.IsNull)
{
if (!_isEmpty)
_accum += ' ';
else
_isEmpty = false;
_accum += value.Value;
}
}
public void Merge(StringConcatAggregate value)
{
Accumulate(value.Terminate());
}
public SqlString Terminate()
{
return new SqlString(_accum);
}
public void Read(BinaryReader r)
{
this.Init();
_accum = r.ReadString();
_isEmpty = _accum.Length == 0;
}
public void Write(BinaryWriter w)
{
w.Write(_accum);
}
}
}
T-SQL do testowania ( CREATE ASSEMBLYi sp_configureaby pominąć CLR):
CREATE TABLE [dbo].[Comments]
(
CustomerCode int NOT NULL,
RowNum int NOT NULL,
Comments nvarchar(25) NOT NULL
)
INSERT INTO [dbo].[Comments](CustomerCode, RowNum, Comments)
SELECT
DENSE_RANK() OVER(ORDER BY FirstName),
ROW_NUMBER() OVER(PARTITION BY FirstName ORDER BY ContactID),
Phone
FROM [AdventureWorks].[Person].[Contact]
GO
CREATE AGGREGATE [dbo].[StringConcatAggregate]
(
@input nvarchar(MAX)
)
RETURNS nvarchar(MAX)
EXTERNAL NAME StringConcatAggregate.[MyCompany.SqlServer.StringConcatAggregate]
GO
SELECT
CustomerCode,
[dbo].[StringConcatAggregate](Comments) AS AllComments
FROM [dbo].[Comments]
GROUP BY CustomerCode