如何在SQL Server 2008中基于Model/Size组合生成含备选SKU的透视视图

问题背景 #

现有Product表结构如下：

需要创建视图，为每个Model/Size组合下的产品展示同组内其他颜色的备选SKU和颜色信息，视图结构包含Model、Size、SKU、Color、AltSKU1、AltColor1、AltSKU2、AltColor2等字段。

实现思路 #

1. 自连接产品表，关联同一Model/Size组合下的其他产品（排除自身）
2. 为每个产品的备选颜色按SKU排序生成唯一编号
3. 通过条件聚合将编号后的备选项转换为指定列，无备选项时自动填充NULL

视图创建SQL代码 #

CREATE VIEW ProductAlternateColors
AS
WITH ProductWithAlternates AS (
    SELECT
        p.Model,
        p.Size,
        p.SKU,
        p.Color,
        alt.SKU AS AltSKU,
        alt.Color AS AltColor,
        -- 为每个产品的备选颜色按SKU排序编号
        ROW_NUMBER() OVER (
            PARTITION BY p.Model, p.Size, p.SKU
            ORDER BY alt.SKU
        ) AS AltNumber
    FROM Product p
    LEFT JOIN Product alt
        ON p.Model = alt.Model
        AND p.Size = alt.Size
        AND p.SKU != alt.SKU -- 排除当前产品自身
)
SELECT
    Model,
    Size,
    SKU,
    Color,
    -- 提取第1个备选项
    MAX(CASE WHEN AltNumber = 1 THEN AltSKU END) AS AltSKU1,
    MAX(CASE WHEN AltNumber = 1 THEN AltColor END) AS AltColor1,
    -- 提取第2个备选项
    MAX(CASE WHEN AltNumber = 2 THEN AltSKU END) AS AltSKU2,
    MAX(CASE WHEN AltNumber = 2 THEN AltColor END) AS AltColor2,
    -- 可根据实际需求扩展更多备选列，比如AltSKU3、AltColor3等
    MAX(CASE WHEN AltNumber = 3 THEN AltSKU END) AS AltSKU3,
    MAX(CASE WHEN AltNumber = 3 THEN AltColor END) AS AltColor3
FROM ProductWithAlternates
GROUP BY Model, Size, SKU, Color
GO

代码说明 #

• CTE子查询：通过自连接实现产品与同组内其他产品的关联，用ROW_NUMBER()为每个产品的备选项生成序号，确保每个备选项对应唯一的列位置
• 条件聚合：使用CASE语句配合MAX函数，将不同序号的备选SKU和颜色转换为对应的列，自动处理无备选项的NULL值
• 可根据实际业务中同组内最多的颜色数量，继续扩展更多AltSKU和AltColor字段

查询结果验证 #

执行SELECT * FROM ProductAlternateColors后，结果与预期一致（修正原预期中MAC Large行的Color笔误，原数据SKU9的Color为Silver）：

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