HelpdeskJBC
asked on
MS SQL querry view question comma sperated values
Hello
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_banan a,
Person2 gets bananas and grapefruits
shown in the table--> ,person_banana,person_grap efruit,
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.
Or
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?
Thanks
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_banan
Person2 gets bananas and grapefruits
shown in the table--> ,person_banana,person_grap
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.
Or
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?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@BCUNNEY
Thank you for your help i changed your code a little bit:
(typo v.split in line 2)
select s.Person_CompId, s.SplitString Fruit, comp_name,s.person_fruit, s.person_firstname, s.person_deleted
from
(
SELECT d.Person_CompId, d.pers_fruit, c.items SplitString, c.rn, d.person_firstname, d.person_deleted
FROM dbo.tbl_inperson d
CROSS APPLY dbo.Split(d.person_fruit, ',') c where Person_Deleted is null
) s join Company on Person_CompId = Company_CompId
The result is now in a friendly format
company id fruit
1 apple
1 banana
1 grapefruit
Last Question: How can i combine the result to get only one company id with all the "fruits" inside the one row?
Thank you
Thank you for your help i changed your code a little bit:
(typo v.split in line 2)
select s.Person_CompId, s.SplitString Fruit, comp_name,s.person_fruit, s.person_firstname, s.person_deleted
from
(
SELECT d.Person_CompId, d.pers_fruit, c.items SplitString, c.rn, d.person_firstname, d.person_deleted
FROM dbo.tbl_inperson d
CROSS APPLY dbo.Split(d.person_fruit, ',') c where Person_Deleted is null
) s join Company on Person_CompId = Company_CompId
The result is now in a friendly format
company id fruit
1 apple
1 banana
1 grapefruit
Last Question: How can i combine the result to get only one company id with all the "fruits" inside the one row?
Thank you
to "group back" into 1 row (per person row), you can use the FOR XML syntax:
http://blog.shlomoid.com/2008/11/emulating-mysqls-groupconcat-function.html
http://blog.shlomoid.com/2008/11/emulating-mysqls-groupconcat-function.html
Hi HelpdeskBJC
Yes as advised by angelIII, use FOR XML to get your separate fruit field/records back into a single field/string per company.
Once you have the solution working, the next task you should do is stress test it to see how performance is.
You may have to consider some indexes or if it is feasible have an actual job that runs overnight or in non peak time to do this work and populate the 'fruit' results.
Yes as advised by angelIII, use FOR XML to get your separate fruit field/records back into a single field/string per company.
Once you have the solution working, the next task you should do is stress test it to see how performance is.
You may have to consider some indexes or if it is feasible have an actual job that runs overnight or in non peak time to do this work and populate the 'fruit' results.
ASKER
Thanks
using the CROSS APPLY technique you will be able to put this into a view, and join even more to the next tables ... a rough syntax sample go like this:
Open in new window