Split the data rows set into multiple rows

[gjs_shankar]

Hi Team,

Based on the comment column, I need to split the multiple rows from the CV . How we can achieve in HANA..?

Thanks in Advance

[Abhishek_Hazra]

Hi gjs_shankar,

Have you gone through this blog post : https://blogs.sap.com/2019/02/10/split-string-into-multiple-rows-using-sql-in-sap-hana/? If you replace the comma with semi-colon, it exactly fits into what you asked for.

Best Regards,
Abhi

[venkateswaran_k]

Hi

Use this SQL.

SELECT 
	"COLUMN A" AS "SHOPPING CART",
	"COLUMN B" AS "SC LINE ITEM" ,
	SUBSTR_REGEXPR('[^;]' IN "COMMENT" OCCURRENCE 1) AS "COMMENT1",	        
        SUBSTR_REGEXPR('[^;]' IN "COMMENT" OCCURRENCE 2) AS "COMMENT2",
	SUBSTR_REGEXPR('[^;]' IN "COMMENT" OCCURRENCE 3) AS "COMMENT3",

FROM 
	<YOUR TABLE>;

[gjs_shankar]

Thank your for your response. Comment can increase n number of times. how we can do any dynamical method

[venkateswaran_k]

For dynamic way of handling you may have to write sql function and call that function here..

For simplicity,

suppose your column is 20 char length, then divide by 2 - maximum 10 occurances will be there

so write 10 lines as above.. if value not there it will show as ?

but query will give output

[venkateswaran_k]

Are you able to find the final soln?