SQL Server同时透视含datetime列的两列的实现问题

你遇到的问题根源是：源表中加入DeviceFirstScannedTime后，该列的不同值会打破原有的分组逻辑——SQL Server会把同一人员但扫描时间不同的行视为独立分组，最终导致透视结果拆分成多行。要实现同时透视计数和日期时间列并保持单行展示，有两种高效的实现方式：

方式一：条件聚合（推荐，SQL Server 2012完全支持） #

先对源数据按人员+日期做聚合，确保同一人员每天只有一条记录（包含当天的扫描时间和考勤计数），再用条件聚合生成目标列：

SELECT
  Firstname,
  Company,
  -- 提取2023-02-06的扫描时间
  MAX(CASE WHEN DeviceScannedDateOnly = '2023-02-06' THEN DeviceFirstScannedTime END) AS [06 Scanned time],
  -- 统计2023-02-06的考勤次数
  SUM(CASE WHEN DeviceScannedDateOnly = '2023-02-06' THEN 1 ELSE 0 END) AS [06/02/23],
  -- 提取2023-02-07的扫描时间
  MAX(CASE WHEN DeviceScannedDateOnly = '2023-02-07' THEN DeviceFirstScannedTime END) AS [07 Scanned Time],
  -- 统计2023-02-07的考勤次数
  SUM(CASE WHEN DeviceScannedDateOnly = '2023-02-07' THEN 1 ELSE 0 END) AS [07/02/23]
FROM (
  -- 先聚合：按人员、公司、日期分组，取当天最早的扫描时间
  SELECT
    Firstname,
    CompanyName AS Company,
    DeviceScannedDateOnly,
    MIN(DeviceFirstScannedTime) AS DeviceFirstScannedTime
  FROM [dbo].[vLDN23_DailyReportForPivot]
  GROUP BY Firstname, CompanyName, DeviceScannedDateOnly
) AS AggregatedData
GROUP BY Firstname, Company;

方式二：结合UNPIVOT与PIVOT（适合多列透视场景） #

如果需要透视的列较多，可以先将计数和时间列拆分成键值对，再统一透视：

SELECT
  Firstname,
  Company,
  [06/02/2023_Count] AS [06/02/23],
  [06/02/2023_Time] AS [06 Scanned time],
  [07/02/2023_Count] AS [07/02/23],
  [07/02/2023_Time] AS [07 Scanned Time]
FROM (
  SELECT
    Firstname,
    CompanyName AS Company,
    -- 拼接日期和类型，生成透视的列标识
    CONVERT(VARCHAR(10), DeviceScannedDateOnly, 103) + '_Count' AS PivotColumn,
    CAST(COUNT(Code) AS VARCHAR(10)) AS PivotValue
  FROM [dbo].[vLDN23_DailyReportForPivot]
  GROUP BY Firstname, CompanyName, DeviceScannedDateOnly
  UNION ALL
  SELECT
    Firstname,
    CompanyName AS Company,
    CONVERT(VARCHAR(10), DeviceScannedDateOnly, 103) + '_Time' AS PivotColumn,
    CONVERT(VARCHAR(23), MIN(DeviceFirstScannedTime), 121) AS PivotValue
  FROM [dbo].[vLDN23_DailyReportForPivot]
  GROUP BY Firstname, CompanyName, DeviceScannedDateOnly
) AS SourceTable
PIVOT (
  MAX(PivotValue)
  FOR PivotColumn IN ([06/02/2023_Count], [06/02/2023_Time], [07/02/2023_Count], [07/02/2023_Time])
) AS PivotTable;

关键说明 #

• 两种方式都先对源数据做了预聚合：通过GROUP BY Firstname, CompanyName, DeviceScannedDateOnly确保同一人员每天只有一条记录，避免因DeviceFirstScannedTime的细微差异导致分组拆分。
• 条件聚合写法更简洁直观，适合固定日期的报表场景；UNPIVOT+PIVOT更灵活，适合需要动态生成透视列的场景（可结合动态SQL实现）。

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