StackOverflow Question #66359957, PostgreSQL

Language: Layout:

DROP TABLE IF EXISTS tbl;
    CREATE TABLE tbl (client_id TEXT, day DATE, counts TEXT, process_ids TEXT);
    INSERT INTO tbl (client_id, day, counts, process_ids)
    VALUES ('abc1', '2021-02-01', '3', 'C1,C2 | C3,C4,C5 | C6,C7');
    INSERT INTO tbl (client_id, day, counts, process_ids)
    VALUES ('abc1', '2021-02-01', '2, 3', 'C10,C11,C12 | C13,C14 # C15,C16 | C17,C18');

WITH seq_0_9 AS (
      SELECT 0 AS d
      UNION ALL SELECT 1 AS d
      UNION ALL SELECT 2 AS d
      UNION ALL SELECT 3 AS d
      UNION ALL SELECT 4 AS d
      UNION ALL SELECT 5 AS d
      UNION ALL SELECT 6 AS d
      UNION ALL SELECT 7 AS d
      UNION ALL SELECT 8 AS d
      UNION ALL SELECT 9 AS d
    ),
    numbers AS (
      SELECT a.d + b.d * 10 + c.d * 100 + 1 AS n
      FROM seq_0_9 a, seq_0_9 b, seq_0_9 c
    ),
    processed AS
      (SELECT client_id,
              day,
              REPLACE(counts, ' ', '') AS counts,
              REPLACE(REPLACE(process_ids, ' ', ''), '|', ',') AS process_ids
       FROM tbl),
    split_pids AS
      (SELECT
         client_id, 
         day,
         counts,
         split_part(process_ids, '#', n) AS process_ids,
         n AS n1
       FROM processed
       CROSS JOIN numbers
       WHERE 
         split_part(process_ids, '#', n) IS NOT NULL
         AND split_part(process_ids, '#', n) != ''),
    split_counts AS
      (SELECT
         client_id, 
         day,
         split_part(counts, ',', n) AS counts,
         process_ids,
         n1,
         n AS n2
       FROM split_pids
       CROSS JOIN numbers
       WHERE
         split_part(counts, ',', n) IS NOT NULL
         and split_part(counts, ',', n) != ''),
    matched_up AS
      (SELECT * FROM split_counts WHERE n1 = n2)
    SELECT
      client_id, 
      day,
      counts,
      split_part(process_ids, ',', n) AS process_ids
    FROM
      matched_up
    CROSS JOIN
      numbers
    WHERE
      split_part(process_ids, ',', n) IS NOT NULL
      AND split_part(process_ids, ',', n) != '';


absolute service time: 0,44 sec