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_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.
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

Barry Cunney

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

to split up the comma-delimited string, you can use this article's function: https://www.experts-exchange.com/Database/Miscellaneous/A_1536-delimited-list-as-parameter-what-are-the-options.html

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:

select p.name
   , p.fruits_selected
   , pf.value fruit_code
   , f.friendly_name fruit_name
  from person p
  cross apply dbo.ParmsToList(p.fruits_selected, ',') pf
  join fruits f 
     on f.fruit_code = pf.value

Open in new window

HelpdeskJBC

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

Guy Hengel [angelIII / a3]

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

Barry Cunney

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.

HelpdeskJBC

ASKER

Thanks