如何按FIELD列分组重复记录并按条件生成新列与值

需要按FIELD列对重复记录进行分组，并根据给定条件添加新列。

样例表 #

建表与插入数据 #

CREATE TABLE [dbo].[TEST](
    [TYPE] [nvarchar](255) NULL,
    [SECTION] [nvarchar](255) NULL,
    [FIELD] [nvarchar](255) NULL,
    [InREPO] [nvarchar](255) NULL)

INSERT INTO [dbo].[TEST]
           ([TYPE]
           ,[SECTION]
           ,[FIELD]
           ,[InREPO])
     VALUES
           ('NDA','Info','Counterparty','TRUE'),
           ('NDA','Info','Country','TRUE'),
           ('NDA','Action','Region','FALSE'),
           ('CIS','Info','Counterparty','TRUE'),
           ('CIS','Action','Country','FALSE'),
           ('CIS','Action','Region','TRUE'),
           ('CIS','Hidden','Address','FALSE')

样例表数据 #

预期结果 #

当前已实现的代码 #

SELECT [FIELD],
       CASE
           WHEN [TYPE] like 'NDA' THEN
               'TRUE'
           ELSE
               'FALSE'
       END AS [NDA],
       CASE
           WHEN [TYPE] like 'NDA' THEN
               [SECTION]
           ELSE
               'n/a'
       END AS [NDA_SECTION],
       CASE
           WHEN [TYPE] like 'NDA' THEN
               [InREPO]
           ELSE
               'n/a'
       END AS [NDA_InREPO],
       CASE
           WHEN [TYPE] like 'CIS' THEN
               'TRUE'
           ELSE
               'FALSE'
       END AS [CIS],
       CASE
           WHEN [TYPE] like 'CIS' THEN
               [SECTION]
           ELSE
               'n/a'
       END AS [CIS_SECTION],
       CASE
           WHEN [TYPE] like 'CIS' THEN
               [InREPO]
           ELSE
               'n/a'
       END AS [CIS_InREPO]
FROM [TEST]

当前执行结果 #

修改方案 #

要实现预期的分组聚合效果，需要使用GROUP BY [FIELD]，并结合MAX()函数提取对应TYPE下的有效字段值（每个FIELD对应每个TYPE最多一条记录，MAX()会自动忽略无效值，保留有效内容）。修改后的SQL代码如下：

SELECT 
    [FIELD],
    -- NDA相关字段
    MAX(CASE WHEN [TYPE] = 'NDA' THEN 'TRUE' ELSE 'FALSE' END) AS [NDA],
    MAX(CASE WHEN [TYPE] = 'NDA' THEN [SECTION] ELSE 'n/a' END) AS [NDA_SECTION],
    MAX(CASE WHEN [TYPE] = 'NDA' THEN [InREPO] ELSE 'n/a' END) AS [NDA_InREPO],
    -- CIS相关字段
    MAX(CASE WHEN [TYPE] = 'CIS' THEN 'TRUE' ELSE 'FALSE' END) AS [CIS],
    MAX(CASE WHEN [TYPE] = 'CIS' THEN [SECTION] ELSE 'n/a' END) AS [CIS_SECTION],
    MAX(CASE WHEN [TYPE] = 'CIS' THEN [InREPO] ELSE 'n/a' END) AS [CIS_InREPO]
FROM [TEST]
GROUP BY [FIELD]
ORDER BY [FIELD]

代码说明 #

• GROUP BY [FIELD]：将相同FIELD值的记录合并为一行。
• MAX()函数：对于每个FIELD，每个TYPE要么有有效记录（对应TRUE、实际SECTION/InREPO值），要么无对应记录（对应FALSE、n/a），MAX()会优先保留非无效值的内容，实现同一FIELD下不同TYPE信息的整合。
• ORDER BY [FIELD]：可选，用于让结果按FIELD排序，与预期结果一致。

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