SQL Server Performance

Query with dynamic rows and columns?

Discussion in 'SQL Server 2005 General Developer Questions' started by blackblade, Apr 10, 2006.

  1. blackblade New Member

    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!
  2. simondm New Member

    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.

  3. blackblade New Member

  4. mmarovic Active Member

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

Share This Page