SQL数据透视表聚合：按日期行转列的仓库库存查询问题求助

需求：查询各仓库的商品信息，将当月日期作为列，对应显示库存数量（需动态生成当月日期列）。编写SQL时遇到聚合错误，参考同类示例后仍无法解决，寻求解决思路。

I. 表创建语句 #

CREATE TABLE [dbo].[Warehouse_test](
    [Date_gen] [date] NOT NULL,
    [Warehouse] [char](10) NOT NULL,
    [Prod_Id] [int] NOT NULL,
    [quantity] [int] NULL,
 CONSTRAINT [PK_Warehouse_test] PRIMARY KEY CLUSTERED 
(
    [Date_gen] ASC,
    [Warehouse] ASC,
    [Prod_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

II. 测试数据插入语句 #

INSERT INTO [dbo].[Warehouse_test]
           ([Date_gen]
           ,[Warehouse]
           ,[Prod_Id]
           ,[quantity])
     VALUES
        ('2023-07-01','MAG01',2145,586),
    ('2023-07-02','MAG01',2145,581),
    ('2023-07-03','MAG01',2145,344),
    ('2023-07-02','MAG01',2186,9),
    ('2023-07-01','MAG02',753,295),
    ('2023-07-02','MAG02',753,31),
    ('2023-07-03','MAG02',753,295),
    ('2023-07-02','MAG04',2186,2),
    ('2023-07-01','MAG14',2145,7),
    ('2023-07-02','MAG14',2145,111),
    ('2023-07-03','MAG14',2145,11)
GO

III. 尝试的查询语句及报错 #

方案1：CASE表达式（非动态） #

-- 1 Option 
 select   [Warehouse], [Prod_Id],  
      (case when [Date_gen] = '2023-07-01' then [quantity] end) '1',
      (case when [Date_gen] = '2023-07-02' then [quantity] end) '2',
      (case when [Date_gen] = '2023-07-03' then [quantity] end) '3'
   from
(
  select [Warehouse], [Prod_Id], [quantity], [Date_gen]
   -- , row_number() over(partition by  [Warehouse], [Prod_Id] order by [Warehouse], [Prod_Id]) rn
  from [dbo].[Warehouse_test]
 
  group by  [Warehouse], [Prod_Id], [quantity] , [Date_gen]
 )
src

报错信息：

Column 'src.Warehouse' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

（注：尝试添加MAX()聚合后仍未解决，原注释代码为尝试的聚合写法）

方案2：PIVOT动态行转列 #

-- 2 option pivot table
--Msg 402, Level 16, State 1, Line 147
--The data types nvarchar(max) and varchar are incompatible in the subtract operator.

DECLARE
@cols   nvarchar(max)='' ,
@query  nvarchar(max)='' 

SET @cols = STUFF((SELECT
 ', ' + replace([Date_gen], '-', '')  as [Date_gen] 

   from [dbo].[Warehouse_test]
   GROUP BY  replace([Date_gen], '-', '')
   FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'');
        --            FOR XML PATH('')), 1, 1, '');


  set @query = 'SELECT [Warehouse], [Prod_Id],   ' + @cols + ' 
            from 
            (
              select [Warehouse], [Prod_Id],  
        
                 replace([Date_gen], '-', '')  as [Date_gen]   , [quantity]
              from [dbo].[Warehouse_test]            
              ORDER BY [Warehouse] DESC , [Prod_Id] DESC

             ) x
            pivot 
            (
                sum([quantity]) 
                for   replace([Date_gen], '-', '') in (' + @cols + ') ) p '

execute(@query);

报错信息：

Msg 402, Level 16, State 1, Line 147
The data types nvarchar(max) and varchar are incompatible in the subtract operator.

解决思路及正确代码 #

问题分析 #

1. 方案1错误原因：外层查询未对Warehouse和Prod_Id做分组，且CASE表达式未配合聚合函数使用，导致数据库无法确定如何合并同一仓库商品的多行数据。
2. 方案2错误原因：动态SQL中，replace([Date_gen], '-', '')在PIVOT的FOR子句中写法错误，且变量拼接时未正确处理列名的引号，同时Date_gen是date类型，直接replace会触发隐式转换导致类型不兼容。

正确动态行转列代码 #

DECLARE @cols NVARCHAR(MAX) = '';
DECLARE @query NVARCHAR(MAX) = '';

-- 生成当月日期列名（格式如20230701），并用QUOTENAME包裹避免语法错误
SET @cols = STUFF((SELECT DISTINCT
    ', ' + QUOTENAME(CONVERT(VARCHAR(8), Date_gen, 112))
FROM [dbo].[Warehouse_test]
WHERE DATEPART(MONTH, Date_gen) = DATEPART(MONTH, GETDATE()) -- 筛选当月数据，可替换为指定月份
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

-- 拼接动态PIVOT查询语句
SET @query = N'
SELECT Warehouse, Prod_Id, ' + @cols + N'
FROM (
    SELECT 
        Warehouse,
        Prod_Id,
        CONVERT(VARCHAR(8), Date_gen, 112) AS Date_col, -- 转换日期为无横杠字符串
        quantity
    FROM [dbo].[Warehouse_test]
    WHERE DATEPART(MONTH, Date_gen) = DATEPART(MONTH, GETDATE()) -- 筛选当月数据
) AS SourceData
PIVOT (
    SUM(quantity) -- 因主键约束，每个日期-仓库-商品唯一，SUM/MAX/MIN结果一致
    FOR Date_col IN (' + @cols + N')
) AS PivotTable
ORDER BY Warehouse, Prod_Id;';

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

代码说明 #

• 使用QUOTENAME()包裹列名，避免特殊字符或关键字导致语法错误。
• 将Date_gen转换为VARCHAR(8)格式（如20230701），解决类型转换不兼容问题。
• 添加当月筛选条件，确保只处理目标月份的日期数据。
• 由于表主键是Date_gen+Warehouse+Prod_Id，每个组合唯一，使用SUM()/MAX()/MIN()聚合函数结果一致，任选其一即可。

内容的提问来源于stack exchange，提问作者Grzegorz Mikłaszewski