How to dynamically pivot non-numerical data?

How to dynamically pivot non-numerical data?

I can create a view with TSQL that pivots my data, but I want the column list to be dynamic so I'd have to use dynamic SQL (regardless of whether I'm using the PIVOT or SELECT CASE method). This would mean the view must be recreated and then updated within LightSwitch when the data to be pivoted to columns changes, which isn't acceptable for me.

If I use WCF RIA Services I'm met with the same problem, the table structure in the code would need updated manually and the data source updated within LightSwitch.

I seem to be on the right track with the various OLAP controls available, they allow dynamic pivoting, and work fantastic with numeric data. They even work fantastic using non-numeric data as row or column fields, which is halfway there. I however want them used as "Values" within my pivot table, but the OLAP controls stick me with using the count() aggregate function on them in this case; something like max() as I'd do with TSQL isn't available.

Here's a couple pictures to illustrate. Here's what I want, except instead of the count() operator being used on my BoughtOn field because it's non-numeric, I want max() used instead (or no aggregate operator at all, whatever) so that I see the actual date. It's worth pointing out that although it doesn't make much sense with this book-related example, on my actual data there won't be more than one instance of the type per row (wouldn't be two+ fantasy books for the owner) so max() if available would work fine, it'd pull the date out amongst null values.

Want but dates not 1s due to count():

Instead the closest I am able to get to something functional is this:

which looks fine with my small example, but for my real data I need (want) each Id to be a single row (compare OwnerId = 2 in the two images).

Is there an OLAP control or something similar for LightSwitch that will let me pivot non-numeric data at run-time in the way that I want? (I've looked at pivot views from ComponentOne, Telerik, and Infragistics.)

Answers & Comments...

---