基于值前缀（yes_）实现PostgreSQL SQL Pivot以消除master_id重复

原始表结构及数据 #

期望结果表 #

方法一：静态Pivot（已知所有yes_前缀属性） #

如果提前明确需要转列的yes_开头属性，直接用静态语句实现（以SQL Server为例）：

SELECT 
    master_id,
    yes_email,
    yes_phone,
    yes_address
FROM (
    SELECT 
        master_id,
        attribute,
        value
    FROM 原始表名
    WHERE attribute LIKE 'yes_%' -- 仅筛选目标前缀属性
) AS SourceTable
PIVOT (
    MAX(value) -- 因同一master_id+attribute无重复，MAX/MIN不影响结果
    FOR attribute IN (yes_email, yes_phone, yes_address)
) AS PivotTable;

方法二：动态Pivot（自动识别所有yes_前缀属性） #

如果yes_开头的属性不固定，用动态SQL自动获取列名（以SQL Server为例）：

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

-- 提取所有yes_开头的attribute列名
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(attribute)
                    FROM 原始表名
                    WHERE attribute LIKE 'yes_%'
                    GROUP BY attribute
                    ORDER BY attribute
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

-- 拼接并执行动态Pivot语句
SET @query = 'SELECT master_id, ' + @cols + ' 
            FROM (
                SELECT master_id, attribute, value
                FROM 原始表名
                WHERE attribute LIKE ''yes_%''
            ) AS SourceTable
            PIVOT (
                MAX(value)
                FOR attribute IN (' + @cols + ')
            ) AS PivotTable';

EXECUTE sp_executesql @query;

MySQL 替代方案（用CASE WHEN模拟Pivot） #

MySQL无原生Pivot语法，可通过条件聚合实现：

SELECT 
    master_id,
    MAX(CASE WHEN attribute = 'yes_email' THEN value END) AS yes_email,
    MAX(CASE WHEN attribute = 'yes_phone' THEN value END) AS yes_phone,
    MAX(CASE WHEN attribute = 'yes_address' THEN value END) AS yes_address
FROM 原始表名
WHERE attribute LIKE 'yes_%'
GROUP BY master_id;

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