SQL Server - Stored Procedure returning table with dynamic columns

We had a stored procedure that returns some data, later we wanted to add some columns to this data, and these columns are dynamic.
We found several articles on how to use pivot to create dynamic columns in tables, for example
http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

and others,

All using the same method, constructing the column headers in a variable, then creating inline sql and using pivot statement and this variable as the columns

In our case we had already a very big select statement, that we didn't want to alter, we just wanted to add more columns to it.

We thought of using table user defined function, but then we had to define the table columns, which we can't do, as it should be dynamic.

We thought also of using a view, then to join with this view, but we can't use pivot in views

We thought also of using a separate stored procedure and calling this stored procedure from the original one, but we found that this is not supported solution.

Finally we tried something and it worked, we alerted the original stored procedure like the following: We inserted the output of the first select statement into a temp table, and then added the second select and pivot in an inline sql statement, and in this statement we inner joined with our temp table.

An example of what we did

Suppose the first statement is like the following

Select * from table1

and our pivot statement is like the following


@query = 'select * from table2 pivot (count(columnName1) for ColumnContainingData in (' + @columnNames + ')'

where columnsName1 is the column that we will perform the aggregate function on, @columnNames is a variable with column names comma seperated and surrounded with []

the final statement looked lke the following

select * into #tempTable from
(select Select * from table1) t
@query = 'select * from table2 pivot (count(columnName1) for ColumnContainingData in (' + @columnNames + ') t2 inner join #tempTable on #tempTable .SomeId = t2.SomeId'