Εναλλακτικα (και εφοσον χρησιμοποιεις SQL 2008) θα μπορουσες να χρησιμοποιησεις μια custom aggregate function
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
[Serializable]
[SqlUserDefinedAggregate
(
Format.UserDefined, //use clr serialization to serialize the intermediate result, Binary Serialization - StringBuilder
IsInvariantToNulls = true, //optimizer property, true= nulls do not change the result
IsInvariantToDuplicates = false, //optimizer property, false = duplicates change the final result
IsInvariantToOrder = false, //optimizer property, false = order changes the result
MaxByteSize = -1 //maximum size in bytes of persisted value, -1 = size up to 2GB
)
]
public class Concatenate : IBinarySerialize
{
/// <summary>
/// The variable that holds the intermediate result of the concatenation
/// </summary>
private StringBuilder intermediateResult;
private string _delimiter = null;
/// <summary>
/// Initialize the internal data structures
/// </summary>
public void Init()
{
this.intermediateResult = new StringBuilder();
this._delimiter = null;
}
/// <summary>
/// Accumulate the next value, not if the value is null
/// </summary>
/// <param name="value"></param>
/// <param name="delimiter"></param>
public void Accumulate(SqlString value, SqlString delimiter)
{
if (value.IsNull | delimiter.IsNull)
{
return;
}
if (this._delimiter == null)
{
this._delimiter = delimiter.Value;
}
this.intermediateResult.Append(delimiter.Value).Append(value.Value);
}
/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(Concatenate other)
{
if (this._delimiter == null)
{
this._delimiter = other._delimiter;
}
this.intermediateResult.Append(other.intermediateResult.ToString());
}
/// <summary>
/// Called at the end of aggregation, to return the results of the aggregation.
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
string output = null;
if (this.intermediateResult != null
&& this.intermediateResult.Length > 0)
{
output = this.intermediateResult.ToString().Remove(0, this._delimiter.Length);
}
return new SqlString(output);
}
public void Read(BinaryReader r)
{
this._delimiter = r.ReadString();
this.intermediateResult = new StringBuilder(r.ReadString());
}
public void Write(BinaryWriter w)
{
w.Write(this._delimiter);
w.Write(this.intermediateResult.ToString());
}
}
Build the project (υποθετωντας με ονομα MyConcatenateProject)
και deploy στον SQL Server ή χρησιμοποιεις T-SQL
πχ
CREATE ASSEMBLY MyConcatenateProject AUTHORIZATION dbo
--pay attention to authorization especially if a sql login is the db owner and the database is moved to another server(assembly owner conflict)
FROM 'c:\MyConcatenateProject.dll'
WITH PERMISSION_SET = SAFE
κ στην συνεχεια δημιουργεις την sql aggregate function
CREATE AGGREGATE dbo.fn_concatit(@value NVARCHAR(MAX), @delimiter NVARCHAR(50))
RETURNS NVARCHAR(MAX)
EXTERNAL NAME MyConcatenateProject.Concatenate
την οποια χρησιμοποιεις οπως τις system aggregate functions (MAX(), COUNT(), AVG() κτλ)
πχ.
select id, dbo.fn_concatit(description, N',') as mydescription
from dbo.myTable
group by id
Η custom aggregate function δεν ειναι bound σε κανενα πινακα (η tsql udf ειναι) οποτε προσφερει μια ποιο "γενικη" λυση.
Φυσικα το προβλημα με το order by λυνεται με μη-συμβατικο τροπο :
SELECT id, dbo.fn_concatit(t.description, N',')
FROM
(
SELECT TOP 100 PERCENT id, description, order_fld
FROM dbo.mytable
GROUP BY id, order_fld, description
ORDER BY id, order_field
) AS t
GROUP BY t.id
ή
--
SELECT t.id, dbo.fn_concatit(t.description, N',')
FROM
( --not that elegant but it does the trick
SELECT TOP (99.999999999999) PERCENT id, description, order_fld
FROM dbo.mytable
ORDER BY id, order_field
) AS t
GROUP BY t.id
ή
SELECT t.id, dbo.fn_concatit(t.description, N',')
FROM
( --not that elegant but it does the trick
SELECT TOP (9223372036854775807) id, description, order_fld
FROM dbo.mytable
ORDER BY id, order_field
) AS t
GROUP BY t.id
Ο κωδικας για την CLR (με hardcoded delimiter το κομμα ",") προσφερεται στα samples του SQL Server : http://msdn.microsoft.com/en-us/library/ms131056.aspx
--ΗΤΗ--