troubleshooting Question

Pivoting SQL in ASP with multiple key columns

Avatar of FreechoiceIT
FreechoiceIT asked on
ASPC#Microsoft SQL Server 2005
7 Comments1 Solution1958 ViewsLast Modified:
Hi All,

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:

http://mytechnicalcorner.blogspot.com/2008/08/pivot-sql-server-2005-with-sample.html

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!:)

Thanks, Charles
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros