SQL拆分数组型字段：提取Answer与Comment列的实现方案

我有一个数据库表，其中Response字段存储的内容格式如下：

Question 1 : ["Yes","","b1"];  Question 2 : ["","No","b2"];  Question 3: ["Yes","","b3"];  Question 4: ["","No",""];  Question 5: ["Yes","","b5"];  Question 6: ["","No","b6"];  Question 7: ["Yes","","b7"];  

为了报表统计，需要将该字段拆分为Question、Answer（仅为"Yes"或"No"）、Comment三列。目前已实现Question列的提取，但无法填充Answer和Comment列，现有SQL代码如下：

SELECT
    *,
    CASE 
        WHEN LEN(ItemValues) > 1 THEN 
            LEFT(ItemValues, charindex(':', ItemValues) - 1)
    END as Question,
    '' as Answer,
    '' as Comment
FROM 
(
    SELECT 
        ID,
        TRIM(value) as ItemValues
    FROM 
        #StackQuestion
    CROSS APPLY STRING_SPLIT(Response,';')
) t1
where
    LEN(ItemValues) > 1

测试表创建及数据插入语句：

Create Table #StackQuestion
(
    ID int IDENTITY(1,1), 
    Response varchar(2000) 
)

insert into #StackQuestion
(
    Response
)
select
'
Question 1 : ["Yes","","b1"]; 
Question 2 : ["","No","b2"]; 
Question 3: ["Yes","","b3"]; 
Question 4: ["","No",""]; 
Question 5: ["Yes","","b5"]; 
Question 6: ["","No","b6"]; 
Question 7: ["Yes","","b7"]; 
'
union all
select
'
Question 1 : ["","No","comment1"]; 
Question 2 : ["","No","c2"]; 
Question 3: ["Yes","","c3"]; 
Question 4: ["Yes","","c4"]; 
Question 5: ["Yes","","b5"]; 
Question 6: ["","No","b6"]; 
Question 7: ["Yes","","b7"]; 
'

实现思路 #

1. 提取JSON数组片段：从每个ItemValues中截取冒号后的[]包裹内容，这是标准JSON数组格式。
2. 解析JSON数组：用OPENJSON将数组转换为行集，获取三个元素的值——第一个对应"Yes"（空则忽略），第二个对应"No"（空则忽略），第三个是评论内容。
3. 填充Answer列：从数组前两个元素中取非空的Yes/No值。
4. 填充Comment列：直接取数组第三个元素的内容，空值保留为空。

完整SQL代码 #

SELECT
    t1.ID,
    TRIM(LEFT(t1.ItemValues, CHARINDEX(':', t1.ItemValues) - 1)) AS Question,
    -- 提取Answer：优先取Yes，空则取No
    COALESCE(j.Value1, j.Value2) AS Answer,
    -- 提取Comment：数组第三个元素
    j.Value3 AS Comment
FROM 
(
    SELECT 
        ID,
        TRIM(value) AS ItemValues
    FROM 
        #StackQuestion
    CROSS APPLY STRING_SPLIT(Response, ';')
) t1
-- 解析JSON数组，映射三个元素到对应变量
CROSS APPLY (
    SELECT 
        MAX(CASE WHEN [key] = 0 THEN TRIM(value) END) AS Value1,
        MAX(CASE WHEN [key] = 1 THEN TRIM(value) END) AS Value2,
        MAX(CASE WHEN [key] = 2 THEN TRIM(value) END) AS Value3
    FROM OPENJSON(
        -- 截取冒号后的JSON数组部分
        SUBSTRING(t1.ItemValues, CHARINDEX(':', t1.ItemValues) + 1, LEN(t1.ItemValues))
    )
) j
WHERE LEN(t1.ItemValues) > 1

代码说明 #

• SUBSTRING(...)：精准截取每个Question对应的JSON数组字符串，去除冒号及之前的内容。
• OPENJSON：将JSON数组拆分为行数据，key对应数组索引（0、1、2），value对应元素内容。
• CASE WHEN：将数组的三个元素分别映射到Value1（Yes位）、Value2（No位）、Value3（评论位）。
• COALESCE(j.Value1, j.Value2)：自动取非空的Yes/No值，符合业务中每个问题只有一个有效答案的规则。

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