如何编写SQL查询实现EAV结构表行转列，将属性行转为对应列？

实现思路 #

这是典型的长表转宽表场景，由于无法按value字段分组，我们采用独立打标再聚合的方案：

1. 分组打标：以AnimalNumber作为每组属性的起始标记，每遇到一次AnimalNumber就生成新的组ID，完全不依赖value字段分组，避免相同value的不同属性组合被错误合并。
2. 行转列聚合：按生成的组ID分组，用条件聚合提取每个属性对应的取值，拼接为最终的宽表结构。

支持窗口函数的数据库通用实现（兼容MySQL8.0+/PostgreSQL/SQL Server等） #

WITH group_mark AS (
    SELECT 
        propertyname,
        propertyvalue,
        value,
        -- 按原始数据顺序排序，每遇到AnimalNumber就将组ID+1，确保同一组属性分到同一个ID
        SUM(CASE WHEN propertyname = 'AnimalNumber' THEN 1 ELSE 0 END) OVER (ORDER BY 你表中保证原始顺序的字段) AS group_id
    FROM db
)
SELECT 
    MAX(CASE WHEN propertyname = 'AnimalNumber' THEN propertyvalue END) AS AnimalNumber,
    MAX(CASE WHEN propertyname = 'Group' THEN propertyvalue END) AS `Group`,
    MAX(CASE WHEN propertyname = 'TimePoint' THEN propertyvalue END) AS TimePoint,
    MAX(CASE WHEN propertyname = 'Treatment method' THEN propertyvalue END) AS `Treatment method`,
    MAX(CASE WHEN propertyname = 'Treatment Conc' THEN propertyvalue END) AS `Treatment Conc`,
    value
FROM group_mark
GROUP BY group_id, value;

低版本MySQL兼容实现（不支持窗口函数场景） #

SET @group_id = 0;
SELECT 
    MAX(CASE WHEN propertyname = 'AnimalNumber' THEN propertyvalue END) AS AnimalNumber,
    MAX(CASE WHEN propertyname = 'Group' THEN propertyvalue END) AS `Group`,
    MAX(CASE WHEN propertyname = 'TimePoint' THEN propertyvalue END) AS TimePoint,
    MAX(CASE WHEN propertyname = 'Treatment method' THEN propertyvalue END) AS `Treatment method`,
    MAX(CASE WHEN propertyname = 'Treatment Conc' THEN propertyvalue END) AS `Treatment Conc`,
    value
FROM (
    SELECT 
        propertyname,
        propertyvalue,
        value,
        @group_id := @group_id + IF(propertyname = 'AnimalNumber', 1, 0) AS group_id
    FROM db
    ORDER BY 你表中保证原始顺序的字段
) t
GROUP BY group_id, value;

注意事项 #

代码中你表中保证原始顺序的字段需要替换为可以还原原始数据排列顺序的字段，比如自增主键、数据插入时间戳等，确保同一个属性组合的5条记录能被分配到同一个组ID。

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