如何用SQL PIVOT实现数据库容量的按月行转列及增长值计算

你现有尝试的PIVOT代码存在三个核心逻辑错配，直接导致结果不符合预期：

• 聚合函数绑定了无关的rowid字段，而非需要展示的容量数值totaldb_size_megs
• 透视列的来源字段错选了ShortDescription，实际应该基于MonthYear的不同枚举值生成对应月份列
• 原始子查询冗余了和固定维度无关的字段（MODIFDT、OldItemNumber），导致PIVOT的分组维度被意外扩展，无法得到按服务器、数据库聚合的结果

PIVOT语法的核心规则：子查询仅保留三类字段——最终输出的固定维度字段、要转成列的分类字段、要做聚合的数值字段，多余字段会直接导致结果异常。

自动时间范围筛选 #

因为原始数据的MonthYear为Mon-YY格式字符串（如Aug-18），首先将其转换为可计算的月度日期值，实现“执行时自动获取当前月往前推算2个自然月的全量统计数据”的要求：

SELECT 
  server_name,
  database_name,
  totaldb_size_megs,
  -- 字符串转标准月度日期：比如'Aug-18' 转换为 '2018-08-01'
  CONVERT(DATE, '01-' + MonthYear) AS stat_month_date
FROM your_capacity_stats_table
-- 筛选范围：当前自然月往前推2个整月（不含当月数据）
WHERE CONVERT(DATE, '01-' + MonthYear) >= DATEADD(MONTH, -2, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
  AND CONVERT(DATE, '01-' + MonthYear) < DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)

正确的PIVOT行转列逻辑 #

基于筛选后的数据集，将两个月份的容量值转为独立列：

WITH filtered_data AS (
  SELECT 
    server_name,
    database_name,
    totaldb_size_megs,
    -- 提取月份缩写作为列名，比如'Aug-18' -> 'Aug'
    LEFT(MonthYear, 3) AS month_short,
    CONVERT(DATE, '01-' + MonthYear) AS stat_month_date
  FROM your_capacity_stats_table
  WHERE CONVERT(DATE, '01-' + MonthYear) >= DATEADD(MONTH, -2, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
    AND CONVERT(DATE, '01-' + MonthYear) < DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
)
SELECT 
  server_name AS Servername,
  database_name AS [Database name],
  Aug,
  Sep
FROM filtered_data
PIVOT (
  -- 聚合容量值，单服务器单数据库单月仅1条统计值，用MAX即可满足需求
  MAX(totaldb_size_megs)
  -- 将month_short字段的枚举值作为独立列
  FOR month_short IN (Aug, Sep)
) AS piv

Growth差值列计算 #

在PIVOT结果基础上，计算相邻两个月份的容量差值，标注对应统计周期：

WITH filtered_data AS (
  SELECT 
    server_name,
    database_name,
    totaldb_size_megs,
    LEFT(MonthYear, 3) AS month_short,
    CONVERT(DATE, '01-' + MonthYear) AS stat_month_date
  FROM your_capacity_stats_table
  WHERE CONVERT(DATE, '01-' + MonthYear) >= DATEADD(MONTH, -2, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
    AND CONVERT(DATE, '01-' + MonthYear) < DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
),
pivoted_result AS (
  SELECT 
    server_name AS Servername,
    database_name AS [Database name],
    Aug,
    Sep
  FROM filtered_data
  PIVOT (
    MAX(totaldb_size_megs)
    FOR month_short IN (Aug, Sep)
  ) AS piv
)
SELECT 
  Servername,
  [Database name],
  Aug,
  Sep,
  -- 计算后一月容量减前一月容量的差值
  ISNULL(Sep, 0) - ISNULL(Aug, 0) AS [Aug-Sep Growth]
FROM pivoted_result

注意：上述示例中写死的Aug、Sep为固定列演示值，实际生产中因为月份列会随执行时间动态变化，需要配合动态SQL实现：先从时间筛选后的数据集里提取所有不重复的月份缩写，动态拼接为PIVOT的IN列表，同时自动生成Growth列的计算表达式，即可适配任意执行时间的场景。

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