Dynamic SQL Rows & Columns...cells require subsequent query. Best approach?

Question

I have the following tables below

City
---------
CityID
StateID
Name
Description

Reports
---------
ReportID
HeaderID
FooterID
Description

I’m trying to generate a grid for use in a .Net control (Gridview, Listview…separate issue about which will be the ‘best’ one to use for my purposes) which will assign the reports as the columns and the cities as the rows.

Which cities get displayed is based on the state selected, and is easy enough

SELECT * FROM CITIES WHERE STATEID=@StateID

However, the user is able to select which reports are being generated for each City (Demographics, Sales, Land Area, etc.).

Further, the resultant cells (City * Report) is a sub-query on different tables based on the city selected and the report.

Ie. Column Sales selected yields

SELECT * FROM SALES WHERE CITYID=@CityID

I’ve programmed a VERY inelegant solution using multiple queries and brute-forcing the grid to be created (line by line, row by row creation of data elements), but I’m positive there’s got to be a better way of accomplishing this…? Any / all suggestions appreciated here as the brute force approach I’ve gotten is slow and cumbersome…and this will have to be used often by the client, so I’m not sure it’ll be acceptable in it’s current implementation.

Answer 1

Since you were having problems with cities not having all the reports, have you tried Left Joins? To get the rows of your grid you can do:

select sales.amt, Demographics.amt, LandArea.amt from cities left join sales on  cities.cityid = sales.cityid 
left join Demographics on  cities.cityid = Demographics.cityid  
left join LandArea on cities.cityid = LandArea.cityid 

The only downside to the above query is that the report types (Demographics, Sales, Land Area, etc.) are hard coded in the query. Maybe you can mix the query above with the dynamic query you mention you tried.

Maybe showing the code for the Grid will help you get better answers?

Hope it helps.