SQL Server多列Pivot分组异常：期望按Group生成单条记录求助

问题原因 #

你当前两次Pivot操作生成多条Group记录的核心问题是：第一次Pivot后，SalesName仍作为结果集的列存在，第二次Pivot会基于Id、Name和SalesName共同分组，导致同一个Group下因不同SalesName拆分出多条记录。

解决方案：条件聚合（推荐） #

使用CASE WHEN配合聚合函数的方式可以直接实现需求，确保每个Group仅返回一条记录，逻辑清晰且易于维护：

DECLARE @tbl TABLE (
    Id INT NOT NULL,
    [Name] VARCHAR(50) NOT NULL,
    GoalName VARCHAR(50) NOT NULL,
    GoalAmount INT NOT NULL,
    SalesName VARCHAR(50) NOT NULL,
    SalesAmount DECIMAL(19,2) NULL
)

INSERT INTO @tbl(Id, [Name], GoalName, GoalAmount, SalesName, SalesAmount)
VALUES (100000, 'Group 1', 'Target 1 Goal', 1000000, 'Target 1 Sales', 31380.00)
     , (100000, 'Group 1', 'Target 2 Goal', 500000, 'Target 2 Sales', 0.00)
     , (100000, 'Group 1', 'Target 3 Goal', 100000, 'Target 3 Sales', 8520.00)
     , (100529, 'Group 2', 'Target 1 Goal', 750000, 'Target 1 Sales', NULL)
     , (100529, 'Group 2', 'Target 2 Goal', 400000, 'Target 2 Sales', NULL)

SELECT 
    Id,
    [Name],
    MAX(CASE WHEN GoalName = 'Target 1 Goal' THEN GoalAmount END) AS [Target 1 Goal],
    MAX(CASE WHEN GoalName = 'Target 2 Goal' THEN GoalAmount END) AS [Target 2 Goal],
    MAX(CASE WHEN GoalName = 'Target 3 Goal' THEN GoalAmount END) AS [Target 3 Goal],
    MAX(CASE WHEN SalesName = 'Target 1 Sales' THEN SalesAmount END) AS [Target 1 Sales],
    MAX(CASE WHEN SalesName = 'Target 2 Sales' THEN SalesAmount END) AS [Target 2 Sales],
    MAX(CASE WHEN SalesName = 'Target 3 Sales' THEN SalesAmount END) AS [Target 3 Sales]
FROM @tbl
GROUP BY Id, [Name]

逻辑说明 #

• 通过GROUP BY Id, [Name]强制按Group维度聚合，确保每个Group仅生成一条记录
• 利用CASE WHEN筛选对应目标的数值，再通过MAX函数提取唯一有效值（同一Group下同一目标仅存在一条数据）

备选方案：单Pivot实现 #

如果坚持使用Pivot语法，需要先将Goal和Sales数据合并为统一结构，再执行一次Pivot：

DECLARE @tbl TABLE (
    Id INT NOT NULL,
    [Name] VARCHAR(50) NOT NULL,
    GoalName VARCHAR(50) NOT NULL,
    GoalAmount INT NOT NULL,
    SalesName VARCHAR(50) NOT NULL,
    SalesAmount DECIMAL(19,2) NULL
)

INSERT INTO @tbl(Id, [Name], GoalName, GoalAmount, SalesName, SalesAmount)
VALUES (100000, 'Group 1', 'Target 1 Goal', 1000000, 'Target 1 Sales', 31380.00)
     , (100000, 'Group 1', 'Target 2 Goal', 500000, 'Target 2 Sales', 0.00)
     , (100000, 'Group 1', 'Target 3 Goal', 100000, 'Target 3 Sales', 8520.00)
     , (100529, 'Group 2', 'Target 1 Goal', 750000, 'Target 1 Sales', NULL)
     , (100529, 'Group 2', 'Target 2 Goal', 400000, 'Target 2 Sales', NULL)

-- 先合并Goal和Sales数据为透视结构
WITH PivotSource AS (
    SELECT 
        Id,
        [Name],
        CONCAT('Goal_', GoalName) AS PivotColumn,
        CAST(GoalAmount AS DECIMAL(19,2)) AS PivotValue
    FROM @tbl
    UNION ALL
    SELECT 
        Id,
        [Name],
        CONCAT('Sales_', SalesName) AS PivotColumn,
        SalesAmount AS PivotValue
    FROM @tbl
)
SELECT 
    Id,
    [Name],
    [Goal_Target 1 Goal] AS [Target 1 Goal],
    [Goal_Target 2 Goal] AS [Target 2 Goal],
    [Goal_Target 3 Goal] AS [Target 3 Goal],
    [Sales_Target 1 Sales] AS [Target 1 Sales],
    [Sales_Target 2 Sales] AS [Target 2 Sales],
    [Sales_Target 3 Sales] AS [Target 3 Sales]
FROM PivotSource
PIVOT (
    MAX(PivotValue)
    FOR PivotColumn IN (
        [Goal_Target 1 Goal], [Goal_Target 2 Goal], [Goal_Target 3 Goal],
        [Sales_Target 1 Sales], [Sales_Target 2 Sales], [Sales_Target 3 Sales]
    )
) AS PivotedData

逻辑说明 #

• 通过UNION ALL将Goal和Sales数据合并为包含统一透视列的数据集
• 执行一次Pivot即可完成所有字段的列转行，最后通过列重命名匹配需求格式

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