We use a MS SQL Server 2008 R2.
I would like to create a view on the SQL Server, but there are some problems:
we have a front end CRM system with a list of data stored in the table "person".
This list is multiselectable and saves the data comma separated in one column for each person.
Lets say Person1 gets apples and bananas
shown in the table--> ,person_apple,person_banana,
Person2 gets bananas and grapefruits
shown in the table--> ,person_banana,person_grapefruit,
Both Person1 and Person2 work for company1 and I need to know only on company level how much fruits they get.
result should look like:
Company1 apple banana grapefruit
The fruits could be in extra columns or in one column.
I would prefer to create a view which is able to calculate this.
The only other way is to generate a macro "cleanup" and generate the report in excel (do some loops and clean up each person and sum up the "fruits" in one cell.
We could generate a selection field on company level and fill in manually each time one customer gets changes.
There is no way to change the Person Table or the actual way of saving the "fruits" inside this table. We could generate new Tables or Access query if this would be necessary.
By the way actual we have 17 different "fruits" in the database.
I also found a Table which includes the code name and the friendly name.
Is there a way to manage this inside a view / access with functions?