---

@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

---

oUTPUT.png