Is it possible to have a table-valued function in T-SQL return a table with a variable number of columns?
The column names may simply be 1, 2, …, n.
Right now I have a "string split" function that returns a single-columned 1 x n table, and I pivot the table afterwards to an n x 1 table, but I'd rather streamline the process by returning the correct table format to begin with.
I intend to use a CLR procedure in C# for this function, I just don't know how to set up the user-defined function to return my data in the format I want: with a variable number of columns, dependent on the input string.
It is not possible to return a non-static Result Set from a Table-Valued Function (TVF), whether it be written in T-SQL or .NET / SQLCLR. Only Stored Procedures can dynamically create a Result Set.
Basically, any function needs to return a consistent result type, whether it is a scalar value or a collection (i.e. Result Set).
However, in a SQLCLR stored procedure, you can create a dynamic Result Set via SqlMetaData. As long as you don't have an explicit need to SELECT ... FROM it, then maybe a stored procedure would work.
Of course, you might also be able to get away with doing this in T-SQL, using dynamic SQL to construct a SELECT statement based on the output of your split function.
A lot of this comes down to the exact context in which this functionality needs to be used.