Reorder column values

[Anonymous]

I have a two column table. One of the columns has Employee Id and the other has a list of all the benefits that the employee holds. I want to split by delimiter, a comma, but have the columns sort by the benefit type as well. Here is what the data looks like

Employee ID	Benefits
01	Benefit 1/Family Plan, Benefit 2/Employee Plan,....,....,...,Benefit 7
02	Benefit 1/Employee Plan, Benefit 3, Benefit 7

When I seperate by delimeter I get

Employee ID	Benefits	Benefits_1	Benefits_2	Benefits_3
01	Benefit 1/Family Plan	Benefit 2/Employee Plan	...	...
02	Benefit 1/Employee Plan	Benefit 3	Benefit 7

I would like to end with this table:

Employee ID	Benefits	Benefits_1	Benefits_2	Benefits_3
01	Benefit 1/Family Plan	Benefit 2/Employee Plan	...	...
02	Benefit 1/Employee Plan		Benefit 3

This way all of the smae type plans are in one column together and a blank field shows up if that employee does not have that plan.

[jgeddes · Accepted solution]

I made a simplified dataset.

First, split the Benefits column by comma delimiter into rows

Split the resulting Benefits column into columns by forward slash delimiter to get...

I trimmed Benefits.1 and also replaced the null values in Benefits.2 with 'Has'

Then Pivot Column Benefits.1 using values from Benefits.2 without aggregating

To end up with...

Hope this gets you pointed in the right direction.