Split the value of single field into multiple columns

[#1]

Hi,

I have a following table:

EmpID, Val

100, a/b

200, e/f/g

300, h/i/j/k/l

I want to split the Column 'Val' into Multiple columns to give the following output:

EmpID, Val, Split1, Split2, Split3, Split4, Split5

100, a/b, a, b, -, -, -

200, e/f/g, e,f,g, -, -

300, h/i/j/k/l, h, i, j, k, l

Here, the new columns added, Split*, are dynamic as I'm not sure of the number of splits that are available in each column value.

Can someone please help? Thanks in advance.

[er_mohit]

see the attached file

[maxgro · Accepted solution]

t2:

load * inline

[

EmpID, Val

100, a/b

200, e/f/g

300, h/i/j/k/l

];

tmp: load max(len(KeepChar(Val, '/'))) as numslash Resident t2;

let numslash=Peek('numslash');

TRACE $(numslash);

DROP Table tmp;

set str = '';

FOR i=1 to $(numslash)+1

  set c1=SubField(Val, '/', $(i));

  set c11=Split$(i);

  set str=$(str) $(c1) as $(c11) , ;

  TRACE $(str);

NEXT i;

final:

NoConcatenate load

  $(str)

  EmpID, Val

Resident t2;

DROP Table t2;

[#4]

Thanks Massimo...