Разделение столбца с несколькими значениями в DB2

С помощью токенизации XMLTABLE вы получите результат, соответствующий вашим требованиям.

Пожалуйста, проверьте мой код ниже, а также на dbfiddle.

Я разделил строковые значения с помощью разделителя (ý) из этих токенизированных столбцов CURR_ASSET_TYPE, OPEN_BALANCE, DEBIT_MVMT, CREDIT_MVMT.

-- Step 1: Tokenize all Columns into separate tables

WITH main AS (SELECT * FROM ( values
    ('0023216531', 'CREDITýCREDIT', '315246ý', 'ý-315246', 'ý315246'),
    ('0023216540', 'CREDITý50000ý50000ýCREDITý50000', '2173118.69ý432ýýý', 'ýýýý-461.69', 'ýý14.85ý461.69ý14.84'),
    ('0023216558', 'DEBITýDEBIT', '-5000ý', 'ý', 'ý5000'),
    ('0023216477', 'CREDITý50000ý50000ýCREDITý50000', '121667.58ý25.14ýýý', 'ýýýý-26.8', 'ýý0.83ý26.8ý0.83')
    ) t2 (ID, CURR_ASSET_TYPE, OPEN_BALANCE, DEBIT_MVMT, CREDIT_MVMT)
), --tokenizing 1
tokens_column1 AS (
  SELECT
    id,
    tok AS Tok1,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS rn
  FROM (
    SELECT
      id,
      tok
    FROM main AS t(ID, CURR_ASSET_TYPE, OPEN_BALANCE, DEBIT_MVMT, CREDIT_MVMT),
    XMLTABLE(
      'for $i in tokenize($s, "ý") return <i>{string($i)}</i>'
      PASSING t.CURR_ASSET_TYPE AS "s"
      COLUMNS tok VARCHAR(100) PATH '.'
    ) AS xt
  ) ORDER BY rn asc
),  --tokenizing 2

tokens_column2 AS (
  SELECT
    id,
    tok AS Tok2,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS rn
  FROM (
    SELECT
      id,
      tok
    FROM 
      main
     AS t(ID, CURR_ASSET_TYPE, OPEN_BALANCE, DEBIT_MVMT, CREDIT_MVMT),
    XMLTABLE(
      'for $i in tokenize($s, "ý") return <i>{string($i)}</i>'
      PASSING t.OPEN_BALANCE AS "s"
      COLUMNS tok VARCHAR(100) PATH '.'
    ) AS xt
  ) ORDER BY rn asc
),  --tokenizing 3

tokens_column3 AS (
  SELECT
    id,
    tok AS Tok3,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS rn
  FROM (
    SELECT
      id,
      tok
    FROM 
      main
     AS t(ID, CURR_ASSET_TYPE, OPEN_BALANCE, DEBIT_MVMT, CREDIT_MVMT),
    XMLTABLE(
      'for $i in tokenize($s, "ý") return <i>{string($i)}</i>'
      PASSING t.DEBIT_MVMT AS "s"
      COLUMNS tok VARCHAR(100) PATH '.'
    ) AS xt
  ) ORDER BY rn asc
),  --tokenizing 4

tokens_column4 AS (
  SELECT
    id,
    tok AS Tok4,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS rn
  FROM (
    SELECT
      id,
      tok
    FROM 
      main
     AS t(ID, CURR_ASSET_TYPE, OPEN_BALANCE, DEBIT_MVMT, CREDIT_MVMT),
    XMLTABLE(
      'for $i in tokenize($s, "ý") return <i>{string($i)}</i>'
      PASSING t.CREDIT_MVMT AS "s"
      COLUMNS tok VARCHAR(100) PATH '.'
    ) AS xt
  ) ORDER BY rn asc
)

-- Step 2: Join the tokenized results as per requirement.
SELECT
  c.id,
  t1.Tok1 AS CURR_ASSET_TYPE,
  t2.Tok2 AS OPEN_BALANCE,
  t3.tok3 AS DEBIT_MVMT,
  t4.tok4 AS CREDIT_MVMT
FROM
  main
   AS c(ID, CURR_ASSET_TYPE, OPEN_BALANCE, DEBIT_MVMT, CREDIT_MVMT)
JOIN tokens_column1 t1 ON c.id = t1.id
JOIN tokens_column2 t2 ON c.id = t2.id AND t1.rn = t2.rn
JOIN tokens_column3 t3 ON c.id = t3.id AND t1.rn = t3.rn
JOIN tokens_column4 t4 ON c.id = t4.id AND t1.rn = t4.rn
ORDER BY c.id, t1.rn;

результат выглядит так.

# |ID        |CURR_ASSET_TYPE|OPEN_BALANCE|DEBIT_MVMT|CREDIT_MVMT|
--+----------+---------------+------------+----------+-----------+
1 |0023216477|CREDIT         |121667.58   |          |           |
2 |0023216477|50000          |25.14       |          |           |
3 |0023216477|50000          |            |          |0.83       |
4 |0023216477|CREDIT         |            |          |26.8       |
5 |0023216477|50000          |            |-26.8     |0.83       |
6 |0023216531|CREDIT         |315246      |          |           |
7 |0023216531|CREDIT         |            |-315246   |315246     |
8 |0023216540|CREDIT         |2173118.69  |          |           |
9 |0023216540|50000          |432         |          |           |
10|0023216540|50000          |            |          |14.85      |
11|0023216540|CREDIT         |            |          |461.69     |
12|0023216540|50000          |            |-461.69   |14.84      |
13|0023216558|DEBIT          |-5000       |          |           |
14|0023216558|DEBIT          |            |          |5000       |

Немного более короткое и простое решение.
Мы используем определение столбца seq for ordinality вместо перечисления каждой строки с помощью функций OLAP.
Также используются выражения xmlpath для удобного преобразования типов данных строка -> десятичный.

WITH multivalue (ID, CURR_ASSET_TYPE, OPEN_BALANCE, DEBIT_MVMT, CREDIT_MVMT) AS(
VALUES
('0023216531', 'CREDITýCREDIT', '315246ý', 'ý-315246', 'ý315246'),
('0023216540', 'CREDITý50000ý50000ýCREDITý50000', '2173118.69ý432ýýý', 'ýýýý-461.69', 'ýý14.85ý461.69ý14.84'),
('0023216558', 'DEBITýDEBIT', '-5000ý', 'ý', 'ý5000'),
('0023216477', 'CREDITý50000ý50000ýCREDITý50000', '121667.58ý25.14ýýý', 'ýýýý-26.8', 'ýý0.83ý26.8ý0.83')
)
SELECT 
  m.id
, c1.seq
, c1.tok as CURR_ASSET_TYPE
, coalesce(c2.tok, 0) as OPEN_BALANCE
, coalesce(c3.tok, 0) as DEBIT_MVMT
, coalesce(c4.tok, 0) as CREDIT_MVMT
FROM 
  multivalue m
, xmltable
(
'for $id in tokenize($s, "ý") return <i>{string($id)}</i>' 
passing 
  m.CURR_ASSET_TYPE as "s"
columns 
  seq for ordinality
, tok varchar(20) path '.'
) c1
, xmltable
(
'for $id in tokenize($s, "ý") return <i>{string($id)}</i>' 
passing 
  m.OPEN_BALANCE as "s"
columns 
  seq for ordinality
, tok decfloat path 'if (. castable as xs:decimal) then xs:decimal(.) else ()'
) c2
, xmltable
(
'for $id in tokenize($s, "ý") return <i>{string($id)}</i>' 
passing 
  m.DEBIT_MVMT as "s"
columns 
  seq for ordinality
, tok decfloat path 'if (. castable as xs:decimal) then xs:decimal(.) else ()'
) c3
, xmltable
(
'for $id in tokenize($s, "ý") return <i>{string($id)}</i>' 
passing 
  m.CREDIT_MVMT as "s"
columns 
  seq for ordinality
, tok decfloat path 'if (. castable as xs:decimal) then xs:decimal(.) else ()'
) c4
WHERE 
    c2.seq = c1.seq
and c3.seq = c1.seq
and c4.seq = c1.seq
order by 1, 2

рабочий пример