Are you saying that you know that a single COL1 value will appear at most 10 times (and thus that the result set will have exactly 11 columns)? Or are you saying that the COL1 value will appear an arbitrary and unknown number of times and so the number of columns in the result set will change?

Assuming you are talking about the latter, you'd need to look at dynamic pivoting. There are multiple examples in the FAQ entry I linked to earlier that discuss how to do this.

It is still not clear to me whether you want a static pivot, where the number of columns is known at compile time, or whether you want a dynamic pivot, where the number of columns is variable and determined at runtime. My guess is that you are looking to do a dynamic pivot. That's why I linked to information on doing a dynamic pivot.

You know that a result set has to have the same number of columns for each row, right? Assuming you expect to return multiple rows (which you do in your third post but not in any of your other examples), the number of columns in the result set will have to be the same for each row.

The FAQ entry that I linked to above walks through different approaches to doing a dynamic pivot. Have you gone through that link?