Dynamic SQL Rows & Columns...cells require subsequent query. Best approach?
- by Pyrrhonist
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.
