I don't think you can do this via T-SQL, BUT you absolutely can accomplish the same thing via a UDF written in C#. Here's the code:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions {
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlString fn_HearingAttendeesToCSV(SqlString tableName, SqlString columnName) {
System.Text.StringBuilder result = null;
using (var cnn = new SqlConnection("context connection=true")) {
var cmd = new SqlCommand();
cmd.CommandText = String.Format("select [{0}] from [{1}]", SqlEscape(columnName.Value), SqlEscape(tableName.Value));
cmd.Connection = cnn;
cnn.Open();
using (var rdr = cmd.ExecuteReader()) {
while (rdr.Read()) {
if (result == null) {
result = new System.Text.StringBuilder();
}
else {
result.Append(",");
}
result.Append(CsvEscape(rdr[0]));
}
}
cnn.Close();
}
if (result == null) {
return SqlString.Null;
}
else {
return result.ToString();
}
}
private static string SqlEscape(string s) {
s = s ?? "";
return s.Replace("[", "[[").Replace("]", "]]");
}
private static string CsvEscape(object o) {
var s = o == null ? "" : o.ToString();
return "\"" + s.Replace("\"", "\"\"") + "\"";
}
};
In order to use CLR UDFs, you need to be sure you've enabled them on the server like this:
exec sp_configure 'clr enabled', 1
RECONFIGURE
You also need to compile to an older version of the .NET framework (2.0 is what I tested with) as SQL 2005 doesn't allow 4.0 assemblies.
Even though your UDF is written in C#, you still access it via T-SQL the same way you're used to:
select fn_HearingAttendeesToCSV('table', 'column')
If you need to support schemas other than 'dbo', or if you don't need pure CSV, modify to fit your needs but this should get you 99% of the way there. That is, assuming you're the DBA or you're good enough buddies with him/her to get CLR enabled.
