SQL Server Performance Forum – Threads Archive

Query with dynamic rows and columns?

How do I handle a query where both the rows and the columns are dynamic? For instance, say I have a set of projects, each of which is being executed by one or more vendors over some time frame. For any given project, I want to write a report that shows their Planned and Actual hours over time. So, for project A, I might need to show the following columns: Month, Vendor 1 plannedhours, Vendor 1 actualhours. But for project B, I might need to show Month, Vendor 1 plannedhours, Vendor 1 actualhours, Vendor 2 plannedhours, Vendor 2 actualhours, etc.. The number of vendors is completely variable for a given project, as is the time frame (the system tracks many projects over multiple years). The underying data tables currently look like this: PlannedResources- projectID int, vendorID int, description varchar, qtyhours decimal, assignmentstart datetime, assignmentend datetime ActualCosts- projectID int, vendorID int, description varchar, qtyhours decimal, chargedate datetime If anyone has any thoughts on how to approach this problem I would love to hear about it. I’ve been forced to come up with ugly workarounds for a few years now and would like to try and handle this in a single, efficient stored procedure if possible. Thanks in advance!

What are you using to produce the reports? In Reporting Services there is a matrix control which is basically a pivot table (from Excel) and will create multiple columns. Obviously your dynamic query already does this. I would change you query to pass the data in a flat format (fixed columns). Then allow the report to pivot the data as needed. I know products like Crystal include similar functinality as well. SImon

Thanks, I will check that out. I also found a really interesting article on how to build a CrossTab stored procedure that appears to do what I want:http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

I agree with Simon. Better do it on the client side if you can.

]]>