SQL Server 2014 如何实现同季度增量累计求和？

该需求为典型的**行转列（透视）**场景，假设源表SalesData结构如下：

目标输出为员工ID、员工姓名作为固定行，每个月份作为单独列存储对应销售金额，可选择以下任意一种实现方式：

方法1：使用内置PIVOT函数实现 #

SQL Server 2005及以上版本均支持，语法简洁直观：

SELECT 
    EmpID,
    EmpName,
    ISNULL(Jan, 0) AS Jan,
    ISNULL(Feb, 0) AS Feb,
    ISNULL(Mar, 0) AS Mar,
    ISNULL(Apr, 0) AS Apr,
    ISNULL(May, 0) AS May,
    ISNULL(Jun, 0) AS Jun,
    ISNULL(Jul, 0) AS Jul,
    ISNULL(Aug, 0) AS Aug,
    ISNULL(Sep, 0) AS Sep,
    ISNULL(Oct, 0) AS Oct,
    ISNULL(Nov, 0) AS Nov,
    ISNULL(Dec, 0) AS Dec
FROM 
(
    -- 源数据子查询，可按需加过滤条件
    SELECT EmpID, EmpName, SaleMonth, SaleAmount
    FROM SalesData
) AS SourceTable
PIVOT
(
    -- 聚合函数可根据需求替换为COUNT/AVG等
    SUM(SaleAmount)
    FOR SaleMonth IN (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)
) AS PivotTable
ORDER BY EmpID;

方法2：使用CASE WHEN+聚合函数实现 #

兼容更低版本SQL Server，逻辑更灵活可自定义复杂规则：

SELECT
    EmpID,
    EmpName,
    SUM(CASE WHEN SaleMonth = 'Jan' THEN SaleAmount ELSE 0 END) AS Jan,
    SUM(CASE WHEN SaleMonth = 'Feb' THEN SaleAmount ELSE 0 END) AS Feb,
    SUM(CASE WHEN SaleMonth = 'Mar' THEN SaleAmount ELSE 0 END) AS Mar,
    SUM(CASE WHEN SaleMonth = 'Apr' THEN SaleAmount ELSE 0 END) AS Apr,
    SUM(CASE WHEN SaleMonth = 'May' THEN SaleAmount ELSE 0 END) AS May,
    SUM(CASE WHEN SaleMonth = 'Jun' THEN SaleAmount ELSE 0 END) AS Jun,
    SUM(CASE WHEN SaleMonth = 'Jul' THEN SaleAmount ELSE 0 END) AS Jul,
    SUM(CASE WHEN SaleMonth = 'Aug' THEN SaleAmount ELSE 0 END) AS Aug,
    SUM(CASE WHEN SaleMonth = 'Sep' THEN SaleAmount ELSE 0 END) AS Sep,
    SUM(CASE WHEN SaleMonth = 'Oct' THEN SaleAmount ELSE 0 END) AS Oct,
    SUM(CASE WHEN SaleMonth = 'Nov' THEN SaleAmount ELSE 0 END) AS Nov,
    SUM(CASE WHEN SaleMonth = 'Dec' THEN SaleAmount ELSE 0 END) AS Dec
FROM SalesData
GROUP BY EmpID, EmpName
ORDER BY EmpID;

动态行转列实现（无需手动枚举所有月份） #

如果列值不固定或不想写死所有月份，可结合动态SQL实现，适配SQL Server 2014版本：

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX)

-- 2014版本用FOR XML PATH拼接所有不重复的月份列
SELECT @columns = STUFF((
    SELECT DISTINCT ',' + QUOTENAME(SaleMonth)
    FROM SalesData
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @sql = N'
SELECT EmpID, EmpName, ' + @columns + '
FROM 
(
    SELECT EmpID, EmpName, SaleMonth, SaleAmount FROM SalesData
) AS Source
PIVOT
(
    SUM(SaleAmount) FOR SaleMonth IN (' + @columns + ')
) AS PivotResult
ORDER BY EmpID'

EXEC sp_executesql @sql

说明 #

• 示例中用ISNULL或ELSE 0将无数据的月份默认填充为0，如需保留NULL值删除对应逻辑即可
• 如果源表字段名、月份存储格式、聚合规则和示例不同，对应调整相关参数即可

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