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
HelpdeskJBCAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Barry CunneyCommented:
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)  
as       
begin      
    declare @idx int       
    declare @slice varchar(8000)       

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

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

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

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

Open in new window


so then the view could do a SELECT something like


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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
to split up the comma-delimited string, you can use this article's function: http://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

0
HelpdeskJBCAuthor Commented:
@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
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
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.
0
HelpdeskJBCAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.