How to dynamically split comma-separated values into multiple rows in SAP Datasphere

[archanareddy_2511]

I am working in SAP Datasphere (Data Builder – Graphical View) and I have a column that contains comma-separated values inside a single row. Example:

ID	Observee(s)
19195	0039270, 0040932, 00051689
43400	00055233
121461	NULL

My requirement is to dynamically split these comma-separated values into individual rows, like this:

ID	Observee
19195	0039270
19195	0040932
19195	00051689
43400	00055233

I need a solution that is 

Dynamic (not limited to a fixed number of values like 5 or 10)

 Works in SAP Datasphere (Graphical View OR HANA SQL

Works even when:

• number of comma-separated values varies per row
• values sometimes contain spaces
• column type is NVARCHAR
• JSON_TABLE or STRING_TO_TABLE are not supported
• Recursion is not enabled in Datasphere SQL View 
• So far, I tried:
• JSON_TABLE() → not supported
• STRING_TO_TABLE() → not supported
• Recursive CTE → not allowed inside Data Builder SQL View
• Regular expression functions → no REGEXP_SUBSTR in Graphical View
• Calculated Columns → no built-in SPLIT or explode function

Is there any recommended Datasphere pattern for splitting CSV string fields into multiple records?

Is this possible in:

• Graphical View (Projection, Aggregation, Calculated Column, etc.)
  or
• SQL View using functions supported by SAP HANA Cloud?

If someone can provide either:

A working SQL example

or

A correct modeling approach in Datasphere

[Sivaramakrishnan_Manickam]

I just tired this in SQL console in SAP HANA , you can try it in SQL View data sphere 

---

@archanareddy_2511 wrote:

I am working in SAP Datasphere (Data Builder – Graphical View) and I have a column that contains comma-separated values inside a single row. Example:

ID	Observee(s)
19195	0039270, 0040932, 00051689
43400	00055233
121461	NULL

My requirement is to dynamically split these comma-separated values into individual rows, like this:

ID	Observee
19195	0039270
19195	0040932
19195	00051689
43400	00055233

I need a solution that is 

Dynamic (not limited to a fixed number of values like 5 or 10)

 Works in SAP Datasphere (Graphical View OR HANA SQL

Works even when:

• number of comma-separated values varies per row
• values sometimes contain spaces
• column type is NVARCHAR
• JSON_TABLE or STRING_TO_TABLE are not supported
• Recursion is not enabled in Datasphere SQL View 
• So far, I tried:
• JSON_TABLE() → not supported
• STRING_TO_TABLE() → not supported
• Recursive CTE → not allowed inside Data Builder SQL View
• Regular expression functions → no REGEXP_SUBSTR in Graphical View
• Calculated Columns → no built-in SPLIT or explode function

Is there any recommended Datasphere pattern for splitting CSV string fields into multiple records?

Is this possible in:

• Graphical View (Projection, Aggregation, Calculated Column, etc.)
  or
• SQL View using functions supported by SAP HANA Cloud?

If someone can provide either:

A working SQL example

or

A correct modeling approach in Datasphere

---

WITH source_data AS (
SELECT 19195 AS id, '0039270,0040932,00051689' AS observees FROM DUMMY
UNION ALL
SELECT 43400, '00055233' FROM DUMMY
UNION ALL
SELECT 121461, NULL FROM DUMMY
UNION ALL
SELECT 55555, '12345,67890' FROM DUMMY
UNION ALL
SELECT 88888, '0001,0002,0003,0004' FROM DUMMY
),
numbers AS (
SELECT 1 AS n FROM DUMMY
UNION ALL SELECT 2 FROM DUMMY
UNION ALL SELECT 3 FROM DUMMY
UNION ALL SELECT 4 FROM DUMMY
UNION ALL SELECT 5 FROM DUMMY
UNION ALL SELECT 6 FROM DUMMY
UNION ALL SELECT 7 FROM DUMMY
UNION ALL SELECT 8 FROM DUMMY
UNION ALL SELECT 9 FROM DUMMY
UNION ALL SELECT 10 FROM DUMMY
)
SELECT
s.id,
TRIM(
CASE
WHEN INSTR(s.observees, ',', 1, n.n) = 0 THEN
SUBSTRING(s.observees,
CASE WHEN n.n = 1 THEN 1 ELSE INSTR(s.observees, ',', 1, n.n - 1) + 1 END,
1000)
ELSE
SUBSTRING(s.observees,
CASE WHEN n.n = 1 THEN 1 ELSE INSTR(s.observees, ',', 1, n.n - 1) + 1 END,
INSTR(s.observees, ',', 1, n.n) -
CASE WHEN n.n = 1 THEN 1 ELSE INSTR(s.observees, ',', 1, n.n - 1) + 1 END
)
END
) AS observee
FROM source_data s
JOIN numbers n
ON n.n <= LENGTH(s.observees) - LENGTH(REPLACE(s.observees, ',', '')) + 1
WHERE s.observees IS NOT NULL
ORDER BY s.id, observee; 

---

@archanareddy_2511 wrote:

I am working in SAP Datasphere (Data Builder – Graphical View) and I have a column that contains comma-separated values inside a single row. Example:

ID	Observee(s)
19195	0039270, 0040932, 00051689
43400	00055233
121461	NULL

My requirement is to dynamically split these comma-separated values into individual rows, like this:

ID	Observee
19195	0039270
19195	0040932
19195	00051689
43400	00055233

I need a solution that is 

Dynamic (not limited to a fixed number of values like 5 or 10)

 Works in SAP Datasphere (Graphical View OR HANA SQL

Works even when:

• number of comma-separated values varies per row
• values sometimes contain spaces
• column type is NVARCHAR
• JSON_TABLE or STRING_TO_TABLE are not supported
• Recursion is not enabled in Datasphere SQL View 
• So far, I tried:
• JSON_TABLE() → not supported
• STRING_TO_TABLE() → not supported
• Recursive CTE → not allowed inside Data Builder SQL View
• Regular expression functions → no REGEXP_SUBSTR in Graphical View
• Calculated Columns → no built-in SPLIT or explode function

Is there any recommended Datasphere pattern for splitting CSV string fields into multiple records?

Is this possible in:

• Graphical View (Projection, Aggregation, Calculated Column, etc.)
  or
• SQL View using functions supported by SAP HANA Cloud?

If someone can provide either:

A working SQL example

or

A correct modeling approach in Datasphere

---