I want to turn/rotate certain information that are displayed in joined rows, so they will become extra columns in the query output.
To illustrate this problem, I have set up an example in which I make use of a small database that contains 3 tables: Products, ProductGroup and Projects. (see the image).
Each time a project is done, different products are used or sold. These are registered in the Product table and are linked to the Project, with the ProjectID. The ProductGroupID in the Product table indicates what kind of group this Product belongs to.
To each project, products are linked that represent costs that are of a certain type and therefore belong to a product group.
I would like to generate an overview of the Projects, where the product groups are dynamically added as columns, to specify the different costs per project. On the image you can see an example of the desired output.
However, there are some additional functionality requirements:
1. The product groups that are be displayed as additional columns in the result, should be selected dynamically, by an parameterized query. In this example there are 2 cost groups; in reality there are hundred different product groups that need to be displayed in various ways.
2. Not all the product groups are filled with costs all the time. Hence the absence of travel costs for the project Cloud puffing in this example.
I have thought of several solutions; but they are either too memory consuming, esp. with large selections, or werent meeting up to one the above condition:
1. Use union statements to add extra columns to the result. In this experiment, it proved to become a cluttered SQL string, that wasnt exactly nicely scalable.
2. I have loaded all Products into an array in ASP.NET first and then iterated through a list of Projects, each time testing if the Product perhaps belonged to both that Project as well as a selected product group. This works okay for small lists, but is quite heavy on the processing and needs a lot of programmatic code.
It would be great to get some hints for directions to take in this puzzle.