Microsoft T-SQL中能否简化SQL PIVOT运算符的极简查询写法？

假设存在一张仅包含state、date、amount三列的表，用于记录每日多笔金额数据。希望通过类似以下极简语法实现透视查询：

select date, sum(amount) pivot by state;

该查询的预期效果是：按日期分组，每行对应一个日期，每个state作为单独列展示当日该州的金额总和。但当前在T-SQL中，必须通过子查询、枚举所有state值或动态SQL才能实现，例如以下代码：

-- 声明动态SQL变量
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- 获取唯一state名称并格式化为PIVOT子句所需形式
SET @columns = STUFF((
    SELECT DISTINCT ',' + QUOTENAME(state)
    FROM Sales
    FOR XML PATH('')
), 1, 1, '', '');

-- 构建动态SQL查询
SET @sql = '
SELECT [date], ' + @columns + '
FROM (
    SELECT [date], [state], [amount]
    FROM Sales
) AS SourceTable
PIVOT (
    SUM(amount)
    FOR state IN (' + @columns + ')
) AS PivotTable;';

-- 执行动态SQL
EXEC sp_executesql @sql;

回答 #

Microsoft Transact-SQL（T-SQL）目前不支持你设想的这种极简pivot by语法。不过可以通过以下方式实现更简洁的动态透视查询：

• 使用SQL Server 2022+的STRING_AGG函数简化代码
  SQL Server 2022及后续版本引入的STRING_AGG函数可以替代旧版的FOR XML PATH拼接列名的方式，让动态SQL代码更简洁：

  DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
  
  -- 使用STRING_AGG直接拼接格式化后的state列名
  SET @columns = STRING_AGG(QUOTENAME(state), ',')
  FROM (SELECT DISTINCT state FROM Sales) AS States;
  
  -- 构建并执行动态SQL
  SET @sql = '
  SELECT [date], ' + @columns + '
  FROM Sales
  PIVOT (SUM(amount) FOR state IN (' + @columns + ')) AS PivotTable;';
  
  EXEC sp_executesql @sql;
  

  这段代码去掉了冗余的子查询嵌套和STUFF函数调用，是当前T-SQL中最接近你需求的简洁实现方式。

• 借助Power Query实现无代码透视
  如果不局限于纯T-SQL环境，使用Power Query（可集成在SSDT、Excel或SSAS中）的透视功能可以自动识别所有唯一state值，无需手动编写动态SQL，操作更直观，生成的M语言代码也远简洁于T-SQL动态SQL。但该方案无法直接在T-SQL脚本中执行。

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