MS SQL querry view question comma sperated values

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?

Who is Participating?
Barry CunneyConnect With a Mentor Commented:
Hi HelpDeskBJC,
I would try and get the database server to do the work - so I would use a database view/function for the solution.
I think that the first idea may be to split out the comma separated values using a table valued function

create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items varchar(MAX), rn int)  
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
            set @slice = @String       

            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       

Open in new window

so then the view could do a SELECT something like

select s.Company,
   v.SplitString   Fruit
  SELECT d.Company, d.delimitedstring, d.delimitedvalues, 
    c.items SplitString, 
  FROM dbo.tblRawData d
  CROSS APPLY dbo.Split(d.DelimitedString, ',') c
) s

Open in new window

Guy Hengel [angelIII / a3]Billing EngineerCommented:
to split up the comma-delimited string, you can use this article's function:

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:
   , 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

HelpdeskJBCAuthor Commented:
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
  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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
to "group back" into 1 row (per person row), you can use the FOR XML syntax:
Barry CunneyCommented:
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.
HelpdeskJBCAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.