使用PIVOT实现动态列SQL语句修改求助：仅返回单行数据

你的问题核心是没有识别同一个bomId下的不同条目组，导致PIVOT把所有同bomKey的值聚合到了一行里。比如bomId=1下有两组part/unit/qty，但原代码没有给它们分组标记，所以MAX()聚合后只保留了最后一个值。

要实现需求，我们需要先给每个bomId下的条目组分配唯一编号，再基于这个组号进行PIVOT，同时动态生成列和拼接ids列。以下是完整的修改方案：

完整动态SQL代码 #

DECLARE @styleId INT = 101; -- 可以替换为103测试
DECLARE @sql NVARCHAR(MAX) = '', @col_list NVARCHAR(MAX) = '';

-- 1. 先获取当前styleId对应的所有bomKey（只取关联的，避免无关列）
SET @col_list = (
    SELECT DISTINCT QUOTENAME(bd.bomKey) + ','
    FROM BomDetail bd
    JOIN MainTable mt ON bd.bomId = mt.id
    WHERE mt.styleid = @styleId
    FOR XML PATH('')
);
SET @col_list = LEFT(@col_list, LEN(@col_list) - 1);

-- 2. 构建动态PIVOT语句，先分组再透视
SET @sql = N'
WITH GroupedBom AS (
    SELECT 
        bd.Id,
        bd.bomId,
        bd.bomKey,
        bd.bomValue,
        -- 每个bomId下，每遇到"part"就开启新分组（因为part是每组的起始标识）
        SUM(CASE WHEN bd.bomKey = ''part'' THEN 1 ELSE 0 END) OVER (
            PARTITION BY bd.bomId 
            ORDER BY bd.Id 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS GroupId
    FROM BomDetail bd
    JOIN MainTable mt ON bd.bomId = mt.id
    WHERE mt.styleid = @styleIdParam
)
SELECT ' + @col_list + ', ''['' + ids + '']'' AS ids
FROM (
    SELECT 
        bomKey,
        bomValue,
        GroupId,
        -- 拼接每个组的Id为逗号分隔字符串
        STRING_AGG(Id, '','') WITHIN GROUP (ORDER BY Id) OVER (PARTITION BY bomId, GroupId) AS ids
    FROM GroupedBom
) AS SourceData
PIVOT (
    MAX(bomValue)
    FOR bomKey IN (' + @col_list + ')
) AS PivotTable
GROUP BY GroupId, ids, ' + @col_list + ';';

-- 执行动态SQL，传入参数
EXEC sp_executesql @sql, N'@styleIdParam INT', @styleIdParam = @styleId;

代码解释 #

1. 分组逻辑：用窗口函数SUM(CASE...) OVER()给每个bomId下的条目分组——每遇到bomKey='part'就累加1，这样同一组的part/unit/qty会被标记为同一个GroupId。
2. 动态列过滤：只获取当前styleId对应的bomKey，避免出现其他style的无关列（比如查询101时不会出现color列）。
3. ids列生成：用STRING_AGG()拼接同一组的所有Id，再手动拼接方括号，让输出格式符合你的期望。
4. 参数化执行：用sp_executesql传入@styleId，避免SQL注入风险，同时让代码更灵活。

测试效果 #

• 当@styleId=101时，会返回两行，分别对应bomId=1下的两个条目组，ids列分别为[1,2,3]和[4,5,6]。
• 当@styleId=103时，返回一行，包含color列，ids列为[10,11,12,13]。

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