I'm currently building a dynamic reporting tools for our stores, all is going well - my code is correctly building an SQL query to match all selections, retrieving the data, and putting it into a datatable.
Pending their selection, i need to pivot the data - for example, on the date column, to show monthly sales for each product across a row. I am familiar with how to pivot in SQL, a simple example here:
I have all of the required data, grouped and summed fully via the original query, and loaded into a datatable for easy binding to a gridview. Is it possible to use datatable.createreader() to create a datareader, and then use standard SQL to pivot the data? How would i do this?
I have found pivot functions online that handle the pivot well when there is only 1 key column, but my results may have many key's, such as breakdowns from Manufacturer, to product line, to actual product, etc.
TL;DR: Can I/How do I run standard SQL as per the above link, on a asp.net C# Datatable?
Otherwise who wants to write a pivot function that supports multiple key columns!:)