Split function in SDI

[Former Member]

Hi Expert,

i have following record in one of my columns. i want to split it into individual rows. Please let me know any trick in SDI

Current table

SR NO	COUNTRY
1	USA, CANADA, INDIA,

Expected output in SDI

SR NO	COUNTRY
1	USA
1	CANADA
1	INDIA

Thanks in advance.

[chandan_praharaj · Accepted solution]

this functionis is there in BODS , but not there in SDI, as per as I know. you need to implement function for this.

[werner_daehn]

How would you do that in Data Services? One option would be turn the string into XML and then convert it into a nested structure which you then unnest. The other option would be to split the string into multiple columns and then use the pivot transform to turn the columns into rows.

The second option should work in SDI as well - have not tried it out myself yet, though.

I would use the substr_regexp() Hana function as mappings in three columns, to get the first, second and third value in each column.

And then use the unpivot transform (unpivot in SQL = Pivot in BODS).

That should work...