Vertical Back to Horizontal

Question

I have a field with with multiple string separated by the ; symbol
Row 1: ca; do; ho;
Row 2: be; do
Row 3: ca; ho

I assign a Record ID to each row and then break of the string and go vertical
1. ca
1. do
1. ho
2. ch
2. do
3. ca
3. ho
I then assign the correct names
1. Cat
1. Dog
1. Horse
2. Chicken
2. Dog
3. Cat
3. Horse
I now need to put them back together horizontally (this is the part I can't figure out how to do.)
Record ID   |   Animals              |
1                   Cat; Dog; Horse
2                   Chicken; Dog
3                   Cat; Horse

This is obviously very simple data and what I really have is complex but if I could figure out the last part of above it will work.

MikeA · Accepted Answer

Hi David,

For this one you can use the summarize tool. You will select your Record ID field and 'Group By" and then you will select your Animals field and "Concatenate". The settings for your summarize tool should look like the below:

Let us know if you have any further questions on this!

PaulT · Accepted Answer

Greetings!

Another way to do it would be to introduce a 3rd field in your data that can be called Header.  This field would contain only one value repeated for each of the records. So in my example:

RecordID   Value    Header
1                 cat        Data
1                 dog       Data
1                 horse    Data

You can accomplish this easily by bringing in a Formula Tool, creating a new string field called Header and using the expression:

"Data"

Now bring in a CrossTab Tool. Here you will Group by RecordID, select Header as your Header Field, and Value as your Data field. In the Methodologies section choose Concatenate, and change the separator from the default comma to the semicolon (you can also use a semicolon and a space if you want your records to be visually spaced a little bit.