SQL提取remark列ITEM与LABEL遇问题，LABEL提取异常求解决

问题背景 #

临时表#test的remark字段存储了包含ITEM和对应LABEL的文本内容，需要生成两列：ITEM列（逗号分隔所有ITEM值）、LABEL列（逗号分隔所有LABEL值）。

样本数据 #

Create table #test (ID int, remark nvarchar(max))

Insert into #test 
values (1,'ITEM:11119:QTY:24 LABEL:00008402279208913782,ITEM:16400:QTY:90 LABEL:00008402279248620756 LABEL:00008402279248620701')

Insert into #test 
values (2,'ITEM:11118:QTY:24 LABEL:00008402279208913782,ITEM:16401:QTY:90 LABEL:00008402279248620756 LABEL:00008402279248620701')

预期输出 #

ID  ITEM            LABEL
--------------------------
1   11119,16400     00008402279208913782,00008402279248620756,00008402279248620701  
2   11118,16401     00008402279208913782,00008402279248620756,00008402279248620701  

已尝试的代码 #

尝试了CTE结合STRING_SPLIT、STRING_AGG的方法，但LABEL提取异常：

WITH SplitItems AS 
(
    SELECT 
        ID,
        Item = SUBSTRING(value, CHARINDEX(':', value) + 1, CHARINDEX(':', value, CHARINDEX(':', value) + 1) - CHARINDEX(':', value) - 1),
        Label = RIGHT(value, LEN(value) - CHARINDEX(':', REVERSE(value)))
    FROM #test
    CROSS APPLY STRING_SPLIT(remark, ',')
),
AggregatedItems AS (
    SELECT
        ID,
        Items = STRING_AGG(Item, ',') WITHIN GROUP (ORDER BY Item) --AS Items
    FROM SplitItems
    GROUP BY ID
),
AggregatedLabels AS (
    SELECT
        ID,
        Labels = STRING_AGG(Label, ',') WITHIN GROUP (ORDER BY [Label]) --AS Labels
    FROM SplitItems
    GROUP BY ID
)
SELECT
    A.ID,
    A.Items AS ITEM,
    L.Labels AS LABLE
FROM AggregatedItems A
JOIN AggregatedLabels L ON A.ID = L.ID;

问题分析与解决方案 #

原代码仅按逗号拆分remark，但每个逗号分隔的片段中可能包含多个LABEL，导致仅提取了片段中的最后一个LABEL。需要先拆分出每个LABEL片段，再进行聚合。

修正后的代码 #

WITH SplitByComma AS (
    -- 按逗号拆分得到每个独立的ITEM块
    SELECT 
        ID,
        ItemBlock = value
    FROM #test
    CROSS APPLY STRING_SPLIT(remark, ',')
),
ExtractItemAndLabels AS (
    SELECT
        ID,
        -- 提取ITEM值：截取第一个冒号与第二个冒号之间的内容
        ITEM = SUBSTRING(ItemBlock, CHARINDEX(':', ItemBlock) + 1, 
                         CHARINDEX(':', ItemBlock, CHARINDEX(':', ItemBlock) + 1) - CHARINDEX(':', ItemBlock) - 1),
        -- 拆分ITEM块中的LABEL片段，截取冒号后的内容
        LABEL = SUBSTRING(LABELPart.value, CHARINDEX(':', LABELPart.value) + 1, LEN(LABELPart.value))
    FROM SplitByComma
    -- 按空格拆分ITEM块，筛选出LABEL开头的片段
    CROSS APPLY STRING_SPLIT(ItemBlock, ' ') AS LABELPart
    WHERE LABELPart.value LIKE 'LABEL:%'
)
SELECT
    ID,
    -- 聚合ITEM，DISTINCT用于去重（可根据业务需求移除）
    ITEM = STRING_AGG(DISTINCT ITEM, ',') WITHIN GROUP (ORDER BY ITEM),
    -- 聚合LABEL，DISTINCT用于去重（可根据业务需求移除）
    LABEL = STRING_AGG(DISTINCT LABEL, ',') WITHIN GROUP (ORDER BY LABEL)
FROM ExtractItemAndLabels
GROUP BY ID;

代码说明 #

1. SplitByComma：将remark按逗号拆分，得到每个包含单个ITEM和对应LABEL的块。
2. ExtractItemAndLabels：
   • 定位ITEM块中的前两个冒号，截取中间的ITEM值。
   • 将ITEM块按空格拆分，筛选出以LABEL:开头的片段，截取冒号后的LABEL内容。
3. 最终通过STRING_AGG分别聚合ITEM和LABEL，DISTINCT用于避免重复值，若业务允许重复可直接移除。

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