Basically, my problem is that I have data that is all bunched together that I need to massage and format so that I can use it in the Group Expert on my report.

In the database, I have company records that have a memo type field for the location of their subsidiaries. The subsidiary data was entered in as State Abbreviation SPACE City ENTER.
So the data looks like this:

CompanyName:
CompanyA

Subsidiaries:
AZ Phoenix
AZ Scottsdale
FL Jacksonville
FL Orlando

CompanyName:
CompanyB

Subsidiaries:
AZ Phoenix
CT Wallingford
DC Washington

What I am trying to do is to Group by the State then by the City. I am currently replacing the Carriage return with a character combination that wont be found elsewhere in the data, then splitting on that to create and array. Then I loop through that array and split on the space in between the state and city. Problem is that this is creating a string of all of the states for that company only where I need them to somehow be combined. See my code below.

dim i as number
dim strLocation as string
strLocation = {vwNACEJCIndexDetails.OppByLocation}
dim strarrLocation() as string
dim strarrState() as string
dim strOutput as string

strLocation = Replace(strLocation, chr(13) + chr(10), "!~!")

strarrLocation = Split(strLocation, "!~!")

if Ubound(strarrLocation) >= 1 then
    for i = 1 to ubound(strarrLocation)
        strarrState = Split(strarrLocation(i), " ")
        strOutput = strOutput + strarrState(1)
    next
else
    strOutput = strOutput + strLocation
end if

formula = strOutput

Any ideas?

Actually, when I browse the data of my formula field, it looks like this:

ALCACACACAFLFLFLLAMAMDMSNMTNTNTNTXVAVA
AZCOCOFLGAIAKSMNMONCNVORTNTXTXWA
AZCOCOFLGAIAKSMNMONCNVORTNTXTXWA
AZCACACACACOCOFLGAMDMDMDMDMNNJNMNYNYOHPASCTXTXTXVA
AZAZFLMN

Which makes sense, as my code is creating an array of states for each company. Each line above would represent a different company. What I am trying to do, is to combine all of the states for all of the companies so that I can have a "master" list of states to group by. If that will even work.

What I was thinking was that once I was able to create that combined master list in my formula field that I would just add the formula field to the Group Expert, in hopes that Crystal would be smart enough to find a match in the Subsidiaries field.

Ok, so I decided to do this in SQL before sending the data to the report. I found a function online to help me split and pivot the data and return it as a table for me. I modified it to accept 2 parameters. The first one being the carriage return that separates the values in the SQL TEXT field, and the second being the space between the state and city values within the first split. I ran this last night and it ran for 45 minutes before I stopped it. Then I tried running it with just one row by selecting top 1 and it still didn't return any records. Can anyone show me how to optimize my function, or a better way to get this done?

Here the modified function:

create function fnsplit (@list varchar(max), @delimiter1 varchar(8), @delimiter2 char(1))
returns @shards table (value1 varchar(8000), value2 varchar(8000))
with schemabinding
as
begin
declare @i int;
set @i = 0;
declare @j int;
set @j = 0;
while @i <= len(@list)
begin
    declare @n int;
    set @n = charindex(@delimiter1, @list, @i);
    if 0 = @n
    begin
       set @n = len(@list);
    end
    while @j <= len(@n)
    begin
          declare @o int;
          set @o = charindex(@delimiter2, substring(@list, @i, @n-@i+1), @j);
          if 0 = @o
          begin
               set @o = len(substring(@list, @i, @n-@i+1));
          end
          insert into @shards (value1,value2)
           values (substring(substring(@list, @i, @n-@i+1), @j, @o-@j+1), substring(substring(@list, @i, @n-@i+1), @o, len(substring(@list, @i, @n-@i+1))));
          set @i = @n+1;
     end
end
return;
end
go


Here is how I have been calling the function:

select columnname
from tablename
cross apply fnsplit(columnname, CHAR(10), ' ');

select top 1 columnname
from tablename
cross apply fnsplit(columnname, CHAR(10), ' ');