SQL实现多列逗号分隔对应值拼接为指定格式字符串

问题原因 #

你原有嵌套REPLACE的写法逻辑错误：该写法会将line_trans_type中的每一个逗号，都替换为整段ref_trans_no替换后的结果，而ref_trans_no的替换又会将每一个逗号替换为整段catalog_item_code替换后的结果，完全无法实现三个字段对应位置元素的匹配，所以会出现值重复的问题。

核心解决思路 #

将三个字段的逗号分隔值分别拆分为带位置序号的行，再按相同位置序号关联三个字段的拆分结果，最后按规则拼接即可。

各数据库实现方案 #

1. SQL Server 2022 及以上版本（支持STRING_SPLIT的ordinal参数）

SELECT 
    LTRIM(t_trans.value) + ' - ' + LTRIM(t_ref.value) + CHAR(9) + LTRIM(t_item.value) AS result
FROM temp_t
CROSS APPLY STRING_SPLIT(line_trans_type, ',', 1) t_trans
CROSS APPLY STRING_SPLIT(ref_trans_no, ',', 1) t_ref
CROSS APPLY STRING_SPLIT(catalog_item_code, ',', 1) t_item
WHERE t_trans.ordinal = t_ref.ordinal 
  AND t_ref.ordinal = t_item.ordinal

2. 低版本SQL Server（无ordinal参数，用XML拆分实现）

WITH split_trans AS (
    SELECT 
        ordinal = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        value = LTRIM(m.n.value('.', 'VARCHAR(1000)'))
    FROM temp_t
    CROSS APPLY (SELECT CAST('<x>' + REPLACE(line_trans_type, ',', '</x><x>') + '</x>' AS XML) AS x) a
    CROSS APPLY x.nodes('/x') m(n)
),
split_ref AS (
    SELECT 
        ordinal = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        value = LTRIM(m.n.value('.', 'VARCHAR(1000)'))
    FROM temp_t
    CROSS APPLY (SELECT CAST('<x>' + REPLACE(ref_trans_no, ',', '</x><x>') + '</x>' AS XML) AS x) a
    CROSS APPLY x.nodes('/x') m(n)
),
split_item AS (
    SELECT 
        ordinal = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        value = LTRIM(m.n.value('.', 'VARCHAR(1000)'))
    FROM temp_t
    CROSS APPLY (SELECT CAST('<x>' + REPLACE(catalog_item_code, ',', '</x><x>') + '</x>' AS XML) AS x) a
    CROSS APPLY x.nodes('/x') m(n)
)
SELECT st.value + ' - ' + sr.value + CHAR(9) + si.value AS result
FROM split_trans st
INNER JOIN split_ref sr ON st.ordinal = sr.ordinal
INNER JOIN split_item si ON st.ordinal = si.ordinal

3. MySQL 8.0 及以上版本

SELECT 
    CONCAT(TRIM(t_trans.trans_type), ' - ', TRIM(t_ref.ref_no), '\t', TRIM(t_item.item_code)) AS result
FROM temp_t
JOIN JSON_TABLE(
    CONCAT('["', REPLACE(line_trans_type, ',', '","'), '"]'),
    '$[*]' COLUMNS (ordinal FOR ORDINALITY, trans_type VARCHAR(1000) PATH '$')
) t_trans
JOIN JSON_TABLE(
    CONCAT('["', REPLACE(ref_trans_no, ',', '","'), '"]'),
    '$[*]' COLUMNS (ordinal FOR ORDINALITY, ref_no VARCHAR(1000) PATH '$')
) t_ref ON t_trans.ordinal = t_ref.ordinal
JOIN JSON_TABLE(
    CONCAT('["', REPLACE(catalog_item_code, ',', '","'), '"]'),
    '$[*]' COLUMNS (ordinal FOR ORDINALITY, item_code VARCHAR(1000) PATH '$')
) t_item ON t_trans.ordinal = t_item.ordinal

内容的提问来源于stack exchange，提问作者Dev