Dynamic SQL Rows & Columns...cells require subsequent query. Best approach?
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 selec开发者_如何学Ct 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.
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.
精彩评论