sql - How to create a function that returns a comma separated list of values? -

I am trying to create a function that is a comma separated list of values ​​based on table name and column name parameter Returns to / P>

This idea was to create a dynamic SQL statement on the fly and then return the result. However, I ran into a problem where I would not give a code to run the execute statement inside. Here's what I did:

  Function fn_HearingAttendeesToCSV (@TableName varchar (100), @ColumnName varchar (100)) back declared as VARCHAR (Max) @sql varchar (max) start selection change @ SQL = "announced @listStr VARCHAR (sELECT MAX) @listStr = COALESCE (@listStr + '', '', '' '') + a '+ @ColumnName +' '+ @TableName +' returns @ ListStr 'Exec (@sql) and live   
 I'm pretty sure I'm going down the wrong road, what a suggestion that  execute  around the problem How should I go? Or a better way of doing this?  
  
 
 
 I do not think you have it in T-SQL, but you can completely c # Only one thing can be written in Here's the code:  
 using the  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; (Var cnn = new SqlConnection (using "context context = true")) {var cmd = new SqlCommand (); cmd.CommandText = String.Format (select "[the [{0}] {1}]", SqlEscape (columnName.Value), SqlEscape (tableName.Value)); CMD Connection = CNN; Cnn.Open (); (Var rdr = cmd.ExecuteReader ()) using {while (rdr.Read ()) {if (result == null) {result = new System.Text.StringBuilder (); } And {Results. Append (","); } Result. (CSVscope (RDR [0]); }} Cnn.close (); } If (result == zero) {return SqlString.Null; } And {return result. Toasting (); }} Private static string SqlEscape (string s) {s = s ?? ""; Return s.Replace ("[", "[["). Change ("]", "]]"); } Private static string CsvEscape (object o) {var s = o == null? "": O. Toasting (); To use CLR UDFs, you can use "" \ "" + s.Replace ("\" "", "\" \ "" + "\" ";;}}   
 Make sure that you need to be active on the server:  
  executive sp_configure 'enable CLR', 1 RECONFIGURE   
 you Also need to compile does not allow SQL 2005 4.0 assemblies, as for an older version of Net Framework (2.0 that I have tested).  
 Even if your UDF C # Written in This T-SQL in the same way you are used to:  
  select fn_HearingAttendeesToCSV to ( 'table', 'column')   
 you Apart from the other schema needs to be 'DBO' or if you do not need a pure CSV, then modify it to meet your needs, but it should get you 99% way. That is, assuming that Are you DBA or are you good friends with him / her CLR enabled To do this.