Fortunately for me the categories of data I crosstab on the server are pretty static. As I said, I absolutely cannot write anything on this server, so my alternatives are "rolling my own" crosstab and possibly adding a grouping once a year or so, or bringing back jillions of rows of data across a slow network and making another pass through the data locally.

It's been a challenge for me to find ways to get the data I want. One of my other challenges was to collect data on a list of about 100 patients. I wrote some code to SCAN my list and build the SQL string with all of them in an IN clause. Worked great! I was able to get the stats I needed in a much smaller amount of time than bringing back way too much data.



>What worries me here is that there are, according to the description, 'dozens' of Categories; an IIF() per, adds up VERY quickly. It also means going in and changing code any time a new Category is added. The join, followed by a Pivot, avoids both problems neatly, and makes best available use of optimization. With a backend, it's even a better approach; less stuff ends up travelling over the wire to get to the point where the pivot can occur; even without a write capability on the backend, you can always create cursors locally on your temp drive (presumably you can write somewhere to create intermediary results, cursors, etc. If you can't even create temporary files, there are some things you just can't do, since arrays have limits, you have to have a swap file, and there are temp files used for general working purposes that you need to be able to create.)