Creating Multiple Records from Single Delimited Field

  • Thread starter Thread starter MSJ
  • Start date Start date
M

MSJ

I am working with some data that lists Supervisor Hierarchy in a Pipe
Delimited format and I want to be able to have it show in a query
based on each level of the supervisor.

Example:
Name SupervisorHierarchy
John Smith |White|Lee|Martinez|Scott|
Jim Jones |Clark|Wright|Jackson|Johnson|Hill|Lewis|

Ideally I would like to see
Name Supervisor Level
John Smith White 1
John Smith Lee 2
John Smith Martinez 3
John Smith Scott 4
Jim Jones Clark 1
Jim Jones Wright 2
Jim Jones Jackson 3
Jim Jones Johnson 4
Jim Jones Hill 5
Jim Jones Lewis 6
 
Is there a maximum number of values in the SupervisorHierarchy field? Do you
have a table of every unique last name? What happens if two people have the
same last name?
 
Back
Top