SQL Server中实现表格行转列需使用什么操作符？附示例

针对你需要将多行Prerequisites信息转换为多列的需求，SQL Server中有两种常用的实现方式：

1. 条件聚合（推荐，灵活性高） #

这种方式适合同时转换多类字段（如状态标识和备注）的场景，代码简洁易维护：

SELECT
    OrganizationCode,
    -- 转换ppco的状态为布尔值
    MAX(CASE 
        WHEN PrerequisitesName = 'ppco' THEN CAST(PrerequisitesValue AS BIT) 
        ELSE CAST(0 AS BIT) 
    END) AS IsPpco,
    -- 提取ppco的备注
    MAX(CASE WHEN PrerequisitesName = 'ppco' THEN Remarks END) AS PpcoRemarks,
    -- 转换liability的状态为布尔值
    MAX(CASE 
        WHEN PrerequisitesName = 'liability' THEN CAST(PrerequisitesValue AS BIT) 
        ELSE CAST(0 AS BIT) 
    END) AS IsLiability,
    -- 提取liability的备注
    MAX(CASE WHEN PrerequisitesName = 'liability' THEN Remarks END) AS LiabilityRemarks
FROM
    YourOriginalTable -- 替换为你的原表名称
GROUP BY
    OrganizationCode;

说明 #

• CASE WHEN用于匹配指定的PrerequisitesName，将对应行的字段值映射到目标列
• MAX聚合函数用于合并同一OrganizationCode下的多行数据（因为每个组织对应每个PrerequisitesName只有一行，用MIN效果相同）
• CAST(PrerequisitesValue AS BIT)将数值1/0转换为SQL Server的布尔类型（显示为true/false）

2. PIVOT操作符 #

PIVOT是SQL Server专门为行转列设计的内置操作符，适合单字段的批量旋转场景。如果需要同时处理状态和备注，可结合CTE（公共表表达式）拆分处理后再关联：

-- 先拆分ppco和liability的数据集
WITH PpcoData AS (
    SELECT
        OrganizationCode,
        CAST(PrerequisitesValue AS BIT) AS IsPpco,
        Remarks AS PpcoRemarks
    FROM YourOriginalTable
    WHERE PrerequisitesName = 'ppco'
),
LiabilityData AS (
    SELECT
        OrganizationCode,
        CAST(PrerequisitesValue AS BIT) AS IsLiability,
        Remarks AS LiabilityRemarks
    FROM YourOriginalTable
    WHERE PrerequisitesName = 'liability'
)
-- 关联两个数据集得到最终结果
SELECT
    p.OrganizationCode,
    p.IsPpco,
    p.PpcoRemarks,
    l.IsLiability,
    l.LiabilityRemarks
FROM PpcoData p
INNER JOIN LiabilityData l 
    ON p.OrganizationCode = l.OrganizationCode;

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