Excel中无聚合的动态透视：实现精确行转列需求

我懂你要的是什么——完全精准的行转列，不想用Sum、Max这类聚合函数来凑数，毕竟之前的方案用聚合总会让人担心会不会丢数据或者取到非预期的值。针对你给出的样本数据，咱们可以通过给每组数据编行号，再配合条件判断或者数据库自带的行转列工具来实现，完全保留原始信息。

先把你的样本数据整理清楚：

ID | User  | DateTime
---|-------|---------------------
2  | UserA | 2018-04-01 22:45:35
2  | UserA | 2018-04-01 13:50:40
2  | UserA | 2018-04-02 05:56:38
2  | UserA | 2018-04-02 14:19:44
2  | UserA | 2018-04-02 14:23:13
2  | UserA | 2018-04-03 05:55:32
2  | UserA | 2018-04-03 05:58:33
2  | UserA | 2018-04-03 14:34:32
2  | UserA | 2018-04-05 13:08:37
3  | UserB | 2018-04-01 13:50:35
...

核心思路是：先给每个分组（ID+User+日期）内的记录分配唯一行号（按时间排序），再根据行号把对应的时间值映射到不同的列中，全程不丢失任何原始数据。

方案1：SQL Server 用 PIVOT（伪聚合，实际不丢数据） #

SQL Server的PIVOT语法要求聚合函数，但咱们可以用MAX来“凑数”——因为每个行号分组下只有一个时间值，MAX其实就是取这个唯一值，完全不影响数据准确性：

WITH RankedData AS (
    SELECT 
        ID,
        [User],
        CAST(DateTime AS DATE) AS RecordDate,
        CAST(DateTime AS TIME) AS RecordTime,
        -- 按ID+User+日期分组，给每条记录编行号
        ROW_NUMBER() OVER (PARTITION BY ID, [User], CAST(DateTime AS DATE) ORDER BY DateTime) AS RowNum
    FROM YourTableName
)
SELECT 
    ID,
    [User],
    RecordDate,
    [1] AS Time1,
    [2] AS Time2,
    [3] AS Time3 -- 可以根据实际最大行数扩展列数
FROM RankedData
PIVOT (
    MAX(RecordTime) -- 这里MAX仅适配语法，实际每个RowNum分组只有一个值
    FOR RowNum IN ([1], [2], [3])
) AS PivotTable
ORDER BY ID, RecordDate;

方案2：MySQL 动态SQL（自动适配可变列数） #

MySQL没有内置PIVOT，所以用动态SQL自动生成需要的列，同样基于行号逻辑：

-- 先获取每个分组的最大行数，确定要生成多少列
SET @max_rows = (
    SELECT MAX(row_count)
    FROM (
        SELECT COUNT(*) AS row_count
        FROM YourTableName
        GROUP BY ID, User, DATE(DateTime)
    ) AS cnt
);

-- 生成列名列表，比如Time1, Time2,...
SET @cols = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN RowNum = ', RowNum, ' THEN RecordTime END) AS Time', RowNum))
INTO @cols
FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY ID, User, DATE(DateTime) ORDER BY DateTime) AS RowNum
    FROM YourTableName
) AS rn
WHERE RowNum <= @max_rows;

-- 拼接完整SQL并执行
SET @sql = CONCAT('
WITH RankedData AS (
    SELECT 
        ID,
        User,
        DATE(DateTime) AS RecordDate,
        TIME(DateTime) AS RecordTime,
        ROW_NUMBER() OVER (PARTITION BY ID, User, DATE(DateTime) ORDER BY DateTime) AS RowNum
    FROM YourTableName
)
SELECT 
    ID,
    User,
    RecordDate,
    ', @cols, '
FROM RankedData
GROUP BY ID, User, RecordDate
ORDER BY ID, RecordDate;
');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

方案3：PostgreSQL 用 crosstab 函数 #

PostgreSQL可以借助tablefunc扩展的crosstab工具，实现更简洁的行转列：
首先启用扩展（仅需执行一次）：

CREATE EXTENSION IF NOT EXISTS tablefunc;

然后执行行转列：

SELECT *
FROM crosstab(
    'SELECT 
        ID || ''_'' || "User" || ''_'' || CAST(DateTime AS DATE) AS group_key,
        ID,
        "User",
        CAST(DateTime AS DATE) AS RecordDate,
        ROW_NUMBER() OVER (PARTITION BY ID, "User", CAST(DateTime AS DATE) ORDER BY DateTime) AS RowNum,
        CAST(DateTime AS TIME) AS RecordTime
     FROM YourTableName
     ORDER BY group_key, RowNum',
    'SELECT generate_series(1, (SELECT MAX(cnt) FROM (SELECT COUNT(*) FROM YourTableName GROUP BY ID, "User", CAST(DateTime AS DATE)) AS t))'
) AS ct(
    group_key text,
    ID int,
    "User" text,
    RecordDate date,
    Time1 time,
    Time2 time,
    Time3 time -- 根据实际最大行数调整列数
)
ORDER BY ID, RecordDate;

注意事项 #

• 以上方案完全保留所有原始时间数据，没有丢弃任何信息；方案1的MAX只是为了适配PIVOT语法，实际不做聚合操作。
• 如果你的分组逻辑不是ID+User+日期，只需要修改PARTITION BY后的字段即可。
• 如果不同分组的记录数不一样，动态SQL（MySQL）或crosstab（PostgreSQL）可以自动适配列数。

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