Tuesday, August 14, 2012 7:05 PM
My question is about the query results on tables in a many-to-many relationship. I have three tables
Products, Applications, ProductApplications
ProductApplications has a FK of the ID from both Products and Applications (this is how I created the many-to-many relationship). ProductApplications also has some information relevent to certain Product and Application combinations.
To work correctly I need to be able to find all products that suit an application. I also need to be able to find all applications suited by a product.
This pretty much works already as long as I correctly write the query, but the query results are hard to work with in an application. For example if I query all Products that suit an application the results would be like
Product1 | Application1 | OtherData
Product1 | Application2 | OtherData
Product1 | Application3 | OtherData
Product2 | Application1 | OtherData
Product2 | Application2 | OtherData
So there are a lot of sorta duplicated rows. How do you work with this at the application level? The ideal output as I imagine it would be
Product1 | Application1 | Application2 | Application3
How do I work with these query results to produce better formatted display in the applicaiton?
Tuesday, August 14, 2012 7:18 PM
What you need is a Dynamic Pivot. Kindly have a look at below link to see how to implement dynamic Pivot.
Please revert if you face any problem.
- Chintak (My Blog)
Wednesday, August 15, 2012 1:59 PM
Personally I think the way it returns is actually better formatted for use in any application.
How the results are actually displayed depend on the application itself.
I believe it's better to leave the formatting to the application.
It should be easy to loop through each record and see when the ProductID changes, and based on that display the Application data.
Even Reporting Services has built-in tools to display data like that however you want - Table Matrix, Stepped Drill-Down etc.
Could you please let us know which application you're using? Is it Reporting Services or is it something you or one of your collegues has written?
Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/
Wednesday, August 15, 2012 2:13 PM
I like you idea - likely because I wouldnt have to change anything. :)
The database will be used by a website developed by other memebers of our team. I'm not sure what development tools they use, but I know it isnt VS - I think they all work on Macs.