SQL JOIN返回重复行：如何将商品属性转为独立列？

原查询代码 #

WITH featured_items
     AS (SELECT DISTINCT cfpi.sku_no,
                         Max(pm.itemsource)
                           OVER (
                             ORDER BY pm.itemsource DESC)
                            itemsource,
                         cm.categoryid,
                         Substr(cm.categoryname, Instr(cm.categoryname, '-') + 1
                         )
                            cat_name
         FROM   category_feat_pick_items cfpi
                JOIN pricemaster pm
                  ON cfpi.sku_no = pm.sku_no
                JOIN categorymaster cm
                  ON cfpi.categoryid = cm.categoryid
         WHERE  cfpi.categoryid = 35801),
     attributes_to_use
     AS (SELECT cfpc.categoryid,
                cfpc.attributeid,
                am.attributename,
                sequence attribute_sequence
         FROM   category_feat_pick_comp cfpc
                JOIN attributemaster am
                  ON cfpc.attributeid = am.attributeid
         WHERE  attribute_option <> 'H'
                AND statusflag <> 'D'
                AND categoryid = 35801),
     compared_items
     AS (SELECT categoryid,
                sku_no,
                itemsource,
                attributeid,
                valueid,
                am.attributename,
                av.valuestring,
                sequence                                  attribute_sequence,
                cat_name,
                Count(DISTINCT sku_no)
                  OVER (
                    partition BY categoryid)              item_count,
                Count(DISTINCT sku_no)
                  OVER (
                    partition BY categoryid, attributeid) att_item_count
         FROM   featured_items s
                JOIN category_feat_pick_comp cfpc using (categoryid)
                JOIN inventorymaster_attributevalue ia using (sku_no,
                attributeid)
                JOIN attributemaster am using (attributeid)
                JOIN attributevalue av using (attributeid, valueid)
                JOIN attributes_to_use atu using (attributeid, categoryid)
         WHERE  ia.statusflag <> 'D')
SELECT *
FROM   compared_items; 

原查询返回结果 #

期望结果 #

问题原因 #

原查询通过JOIN关联了属性值表（inventorymaster_attributevalue）和属性主表，每个SKU的不同属性会生成独立行，这是行式存储属性数据的自然结果，需要通过行转列操作将同一SKU的多属性合并为一行。

修改方案 #

使用**条件聚合（CASE WHEN + 聚合函数）**实现行转列，针对已知的属性名提取对应值：

修改后的SQL代码 #

WITH featured_items
     AS (SELECT DISTINCT cfpi.sku_no,
                         Max(pm.itemsource)
                           OVER (
                             ORDER BY pm.itemsource DESC)
                            itemsource,
                         cm.categoryid,
                         Substr(cm.categoryname, Instr(cm.categoryname, '-') + 1
                         )
                            cat_name
         FROM   category_feat_pick_items cfpi
                JOIN pricemaster pm
                  ON cfpi.sku_no = pm.sku_no
                JOIN categorymaster cm
                  ON cfpi.categoryid = cm.categoryid
         WHERE  cfpi.categoryid = 35801),
     attributes_to_use
     AS (SELECT cfpc.categoryid,
                cfpc.attributeid,
                am.attributename,
                sequence attribute_sequence
         FROM   category_feat_pick_comp cfpc
                JOIN attributemaster am
                  ON cfpc.attributeid = am.attributeid
         WHERE  attribute_option <> 'H'
                AND statusflag <> 'D'
                AND categoryid = 35801),
     compared_items
     AS (SELECT categoryid,
                sku_no,
                itemsource,
                am.attributename,
                av.valuestring,
                cat_name,
                Count(DISTINCT sku_no)
                  OVER (
                    partition BY categoryid)              item_count,
                Count(DISTINCT sku_no)
                  OVER (
                    partition BY categoryid, am.attributeid) att_item_count
         FROM   featured_items s
                JOIN category_feat_pick_comp cfpc using (categoryid)
                JOIN inventorymaster_attributevalue ia using (sku_no, attributeid)
                JOIN attributemaster am using (attributeid)
                JOIN attributevalue av using (attributeid, valueid)
                JOIN attributes_to_use atu using (attributeid, categoryid)
         WHERE  ia.statusflag <> 'D')
SELECT sku_no,
       MAX(CASE WHEN attributename = 'ATTR_1' THEN valuestring END) AS ATTR_1,
       MAX(CASE WHEN attributename = 'ATTR_2' THEN valuestring END) AS ATTR_2,
       cat_name,
       item_count
FROM   compared_items
GROUP BY sku_no, cat_name, item_count;

关键说明 #

1. 核心逻辑：用CASE WHEN匹配属性名，提取对应属性值，再通过MAX聚合函数将同一SKU的多属性行合并为一行（每个SKU对应单个属性值，用MAX/MIN/SUM均可，MAX兼容性更强）。
2. 如果属性数量不固定，不同数据库有动态列实现方案（如Oracle/SQL Server的PIVOT、MySQL的动态SQL），但当前已知属性场景下，条件聚合是最通用的方案。
3. 原查询中不需要的字段（如attributeid、valueid）已从CTE中移除，简化数据处理。

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