如何用MS SQL Server 2016实现项目成本数据SQL转置查询

我需要使用MS SQL Server 2016编写SQL查询获取项目成本数据，用于后续制作SQL报表图表。现有数据存储在如下结构的ProjectCosts表中：

+---------+------------------+----------------+----------------+--------------------+------------------+------------------+
| Project | DevCostsExpected | DevCostsTarget | DevCostsActual | SalesCostsExpected | SalesCostsTarget | SalesCostsActual |
+---------+------------------+----------------+----------------+--------------------+------------------+------------------+
| A       |             1000 |           2000 |           1500 |               2000 |             3000 |             2500 |
| B       |             5000 |           7500 |          10000 |               8000 |            10000 |             3500 |
| C       |             1400 |           1400 |           1000 |               5400 |             6000 |             7500 |
+---------+------------------+----------------+----------------+--------------------+------------------+------------------+

需要将数据转置为如下格式（以项目A、B为例）：

+---------+----------+-------+-------+
| Project |  Costs   |  Dev  | Sales |
+---------+----------+-------+-------+
| A       | Expected |  1000 |  2000 |
| A       | Target   |  2000 |  3000 |
| A       | Actual   |  1500 |  2500 |
| B       | Expected |  5000 |  8000 |
| B       | Target   |  7500 | 10000 |
| B       | Actual   | 10000 |  3500 |
+---------+----------+-------+-------+

方法1：使用UNION ALL实现转置 #

这是最直观的实现方式，通过拆分三类成本维度并合并结果集：

SELECT 
    Project,
    'Expected' AS Costs,
    DevCostsExpected AS Dev,
    SalesCostsExpected AS Sales
FROM ProjectCosts
UNION ALL
SELECT 
    Project,
    'Target' AS Costs,
    DevCostsTarget AS Dev,
    SalesCostsTarget AS Sales
FROM ProjectCosts
UNION ALL
SELECT 
    Project,
    'Actual' AS Costs,
    DevCostsActual AS Dev,
    SalesCostsActual AS Sales
FROM ProjectCosts
ORDER BY Project, Costs;

说明 #

• 每个SELECT语句对应一类成本维度（Expected/Target/Actual），分别提取Dev和Sales的对应字段
• UNION ALL用于合并三个结果集，保留所有行数据
• 最后通过ORDER BY确保结果按项目和成本维度排序，适配报表展示需求

方法2：使用UNPIVOT + PIVOT组合实现 #

如果后续存在字段扩展需求，这种方式灵活性更强：

WITH Unpivoted AS (
    SELECT 
        Project,
        SUBSTRING(CostType, CHARINDEX('Costs', CostType) + 5, LEN(CostType)) AS Costs,
        CASE WHEN CostType LIKE 'Dev%' THEN 'Dev' ELSE 'Sales' END AS Category,
        CostValue
    FROM ProjectCosts
    UNPIVOT (
        CostValue FOR CostType IN (
            DevCostsExpected, DevCostsTarget, DevCostsActual,
            SalesCostsExpected, SalesCostsTarget, SalesCostsActual
        )
    ) AS Up
)
SELECT 
    Project,
    Costs,
    Dev,
    Sales
FROM Unpivoted
PIVOT (
    SUM(CostValue) FOR Category IN (Dev, Sales)
) AS Pv
ORDER BY Project, Costs;

说明 #

1. UNPIVOT阶段：将原表中6个成本字段拆分为Project、Costs（提取Expected/Target/Actual）、Category（Dev/Sales）和CostValue四个字段
2. PIVOT阶段：将Category列的Dev和Sales值转为列，对应聚合后的CostValue
3. 新增成本维度或类别时，仅需调整UNPIVOT中的字段列表即可，无需修改多个SELECT语句

内容的提问来源于stack exchange，提问作者Mec-Eng