How do I make MultiLine Table Valued Function in SQL Server 2008?

I have a query which needs to be joined with almost all the queries for reports. The number of columns this query produces is also not fixed (I am using pivot to get the columns).

I need to make a SQL Function which can be used as a table so that I can join it with other tables so get the desired data. How do I do that?

Is there any other way of doing that? I tried using vi开发者_运维技巧ews, but perhaps views do not support temp tables.

Any suggestions?

Consider using a table-valued user defined function.

A function must produce a result that has a consistent metadata (number, type and order of columns) on each and every invocation. This is not because is a function, but because it would be impossible to create an access path for a function that changes the shape of the return result. This automatically rules out any attempt to do a 'dynamic' pivot using functions or views. Only stored procedures using dynamic SQL can do such and the result cannot be joined directly, must be captured in temp tables and reprocessed. If the resultset changes shape, of course all processing must adapt and be based on dynamic SQL. Typically such 'dynamic' pivots are the realm of presentation and handled in reporting and analysis tools, like Excel, which supports it much better.