If I wanted to replace the id structure (to clean up the data) to the form Lnnn (AG001/1/L001 etc) - is there a way I could do this.

I realise I would wipe the current ID's and then use my 2nd tier ID column (AG001/1) to build the new ID....

So for each ID in column "as_id" create a new id in column ld_id suffixing a 3 digit incremental number on the end of L.......When the as_id changes reset the increment to 0...so it would look like this (in an ideal world):

LD_ID COLUMN AS_ID COLUMN

AG001/1/L001 AG001/1

AG001/1/L002 AG001/1

AG001/1/L003 AG001/1

AG001/2/L001 AG001/1

AG001/2/L002 AG001/1

CE001/1/L001 CE001/1