无聚合函数实现日期与小时的Pivot转换

嘿，我完全懂你这个困扰——用MAX/MIN这类聚合函数做Pivot时，硬生生把同一日期下的多个小时捏成了单个值，根本不是你想要的按小时逐条展示的效果。别慌，咱们调整一下思路和查询结构就能搞定！

问题根源：分组维度搞反了 #

你之前的查询大概率是按日期分组来做Pivot，导致每个日期列只能返回该日期的最大/最小小时，而不是所有关联的小时。要实现「小时为行、日期为列」的Pivot效果，核心是把分组维度换成hour，让每个小时单独占一行，再对每个日期判断该小时是否存在。

解决方案1：静态Pivot（已知所有日期） #

如果你的日期范围是固定的，直接用CASE WHEN结合GROUP BY hour就能实现，这里用MAX/MIN其实完全没问题（因为每个小时+日期组合最多一个值，聚合函数只是满足语法要求）：

SELECT 
  hour,
  MAX(CASE WHEN date = '2024-05-01' THEN hour END) AS '2024-05-01',
  MAX(CASE WHEN date = '2024-05-02' THEN hour END) AS '2024-05-02',
  MAX(CASE WHEN date = '2024-05-03' THEN hour END) AS '2024-05-03'
FROM your_table
GROUP BY hour
ORDER BY hour ASC;

执行后你会得到：每一行对应一个小时，各日期列显示该日期是否包含这个小时（有就显示小时值，没有则为NULL），而且小时会严格按顺序排列。

解决方案2：动态Pivot（日期不固定） #

如果你的日期是动态变化的，没法提前写死列名，就用动态SQL自动生成日期列：

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

-- 自动提取所有不重复的日期，转成列名格式
SELECT @cols = STRING_AGG(QUOTENAME(date), ', ')
FROM (SELECT DISTINCT date FROM your_table) AS dates;

-- 构建动态Pivot查询
SET @query = '
SELECT hour, ' + @cols + '
FROM (
  SELECT date, hour FROM your_table
) AS src
PIVOT (
  MAX(hour) FOR date IN (' + @cols + ')
) AS pvt
ORDER BY hour ASC;';

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

这个方案会自动适配所有存在的日期，结果同样是按小时排序的「小时行+日期列」结构。

额外需求：同一日期显示多个小时（逗号分隔） #

如果你的需求是每个日期列显示该日期下的所有小时（用逗号拼接），可以把聚合函数换成STRING_AGG，比如静态写法：

SELECT 
  hour,
  STRING_AGG(CASE WHEN date = '2024-05-01' THEN CAST(hour AS VARCHAR) END, ', ') 
    WITHIN GROUP (ORDER BY hour) AS '2024-05-01',
  STRING_AGG(CASE WHEN date = '2024-05-02' THEN CAST(hour AS VARCHAR) END, ', ') 
    WITHIN GROUP (ORDER BY hour) AS '2024-05-02'
FROM your_table
GROUP BY hour
ORDER BY hour ASC;

这样同一日期同一小时的记录会被拼接成逗号分隔的字符串（如果有重复的话可以加DISTINCT去重）。

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