“拆分”字段并旋转mysql或talend

CREATE TABLE temp_table (
  id INT,
  field_value VARCHAR(100)
);

INSERT INTO temp_table (id, field_value)
VALUES (1, 'A:10,B:20,C:30'),
       (2, 'A:15,B:25,C:35');

SELECT id,
       MAX(CASE WHEN field_key = 'A' THEN field_value END) AS A,
       MAX(CASE WHEN field_key = 'B' THEN field_value END) AS B,
       MAX(CASE WHEN field_key = 'C' THEN field_value END) AS C
FROM (
  SELECT id,
         SUBSTRING_INDEX(SUBSTRING_INDEX(field_value, ',', n), ':', -1) AS field_value,
         SUBSTRING_INDEX(SUBSTRING_INDEX(field_value, ',', n), ':', 1) AS field_key
  FROM temp_table
  CROSS JOIN (
    SELECT 1 + units.i + tens.i * 10 AS n
    FROM (
      SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
      SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
    ) units
    JOIN (
      SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
      SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
    ) tens
  ) numbers
  WHERE n <= 1 + (LENGTH(field_value) - LENGTH(REPLACE(field_value, ',', ''))))
  AS subquery
GROUP BY id;