Split column value with fixed and dynamic values
I have a column, 'col', that contains data in the following format.
1 lab 1 100
2 lab 1 200|lab3 910
Each value is a pipe delimited name value pair. Names are a fixed set of labels that could contain space, such as 'lab 1'. Values such as 910 and 100 are dynamic.
I'd like to get the data into the format below. Can you please advise how to achieve this? I am looking at operators such as split value and tokenizer, but neither seems to handle this out of the box.
rownum lab 1 lab3
1 100 ?
2 200 910
Thanks very much!