Many-To-Many query results

Question

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

or perhaps....

Product1

                Application1

                Application2

                Application3

How do I work with these query results to produce better formatted display in the applicaiton?

Thanks!

Answer 1

Hi,

What you need is a Dynamic Pivot. Kindly have a look at below link to see how to implement dynamic Pivot.

http://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/

Please revert if you face any problem.

---

- Chintak (My Blog)

Answer 2

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/

Answer 3

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.