In SQL can I return a tables with a varying number of columns
- by Matt
I have a somewhat more complicated scenario, but I think it should be possible.
I have a large SPROC whose result is a set of characteristics for a set of persons.
So the Table would look something like this:
Property | Client1 Client 2 Client3
-----------------------------------------------------------
Sex | M F M
Age | 67 56 67
Income | Low Mid Low
It's built using cursors, iterating over different datasets.
The problem I am facing is that there is a varying number of Clients and Properties, so an equally valid result over different input sets might be:
Property | Client1 Client 2
-------------------------------------------
Sex | M F
Age | 67 56
Weight | 122 122
The different number of properties is easy, those are just extra rows.
My problem is that I need to declare a temporary table with a varying number of columns.
There could be 2 clients or 100. Every client in guaranteed to have every property ultimately listed.
What SQL structure would statisfy this and how can I declare it and insert things into it?
I can't just flip the columns and rows either because there is a variable number of each.
