So you're looking for a dynamic pivot.
Please see the forum FAQ: Re: 4. How do I convert rows to columns?
My answer would be, why would you want a query that produces an undefined number of columns in the results? What code are you going to write that can handle an unknown number of columns?
Typically this type of requirement is something done for reporting purposes, so the best place to do it is in a reporting tool, which itself will usually query the data and then pivot it dynamically to provide the best presentation for the report. It's not something often required inside any code processing. So, perhaps you need to reconsider what the actual requirement is?