如何编写T-SQL实现SQL Server按Category多列分组透视取数

DevOps 小满

2026-9-30

T-SQL 透视实现方案

方案1：静态透视（适用于Category取值固定且数量少的场景）

假设你的原表（或现有查询结果的CTE）包含字段：Date、Store、Category、Sales（可替换为你实际的度量字段名），示例代码如下：

SELECT 
  Date,
  Store,
  ISNULL([Electronics], 0) AS Electronics_Sales,
  ISNULL([Clothing], 0) AS Clothing_Sales,
  ISNULL([Food], 0) AS Food_Sales
  -- 这里按你实际的Category取值依次添加列
FROM 
(
  -- 这里替换为你现有产生未透视结果的查询语句
  SELECT Date, Store, Category, Sales FROM YourOriginalTable
) AS SourceData
PIVOT
(
  SUM(Sales) -- 这里替换为你实际需要的聚合函数，如COUNT、MAX等
  FOR Category IN ([Electronics], [Clothing], [Food]) -- 这里列出所有需要转为列的Category取值
) AS PivotTable
ORDER BY Date, Store;

方案2：动态透视（适用于Category取值不固定、需要自动适配新增类别的场景）

无需手动修改列名，代码会自动读取所有Category取值生成透视列：

DECLARE @Columns NVARCHAR(MAX), @SQL NVARCHAR(MAX)

-- 生成所有Category的列名列表，自动处理空值替换为0
SELECT @Columns = STRING_AGG(QUOTENAME(Category) + ' = ISNULL(' + QUOTENAME(Category) + ', 0)', ', ')
FROM (SELECT DISTINCT Category FROM YourOriginalTable) AS CatList

-- 拼接完整的透视查询语句
SET @SQL = N'
SELECT Date, Store, ' + @Columns + N'
FROM 
(
  SELECT Date, Store, Category, Sales FROM YourOriginalTable
) AS SourceData
PIVOT
(
  SUM(Sales)
  FOR Category IN (' + (SELECT STRING_AGG(QUOTENAME(Category), ', ') FROM (SELECT DISTINCT Category FROM YourOriginalTable) AS CatList) + N')
) AS PivotTable
ORDER BY Date, Store;
'

-- 执行动态查询
EXEC sp_executesql @SQL