Split single delimited by commas column in 5 seperate columns

[Anonymous]

Hi All

I appreciate your time in advance.

I have one column with values like

Origin

1.5,2.3,1.7,4.1,2

I need as below 5 columns in single row

O1    O2   O3   O4   O5

1.5   2.3   1.7   4.1     2

Thanks

[sunny_talwar · Accepted solution]

May be this

LOAD Origin,

     SubField(Origin, ',', 1) as O1,

     SubField(Origin, ',', 2) as O2,

     SubField(Origin, ',', 3) as O3,

     SubField(Origin, ',', 4) as O4,

     SubField(Origin, ',', 5) as O5

FROM ....;

[kaushiknsolanki]

Hi,

Try this.

Load SubField('1.5,2.3,1.7,4.1,2',',',1) as Field1,

         SubField('1.5,2.3,1.7,4.1,2',',',2) as Field2,

SubField('1.5,2.3,1.7,4.1,2',',',3) as Field3,

SubField('1.5,2.3,1.7,4.1,2',',',4) as Field4,

SubField('1.5,2.3,1.7,4.1,2',',',5) as Field5

From xyx

Regards,

Kaushik Solanki

[vamsee]

Hi,

please find the attached. May be not the optimal one but works.

Thanks

Vamsee

[Anonymous]

Thanks Sunny, Correct