如何将CAT_EXTRACTTIMES表按TimeLogged分组并转置类别列

我有一张记录待处理案例的CAT_EXTRACTTIMES表，结构及数据如下：

Wait_Time || Category || TimeLogged
1230      ||     3    || 2024-01-18 23:50:00.0000000
1245      ||     2    || 2024-01-18 23:50:00.0000000
1230      ||     3    || 2024-01-18 23:55:00.0000000
1260      ||     1    || 2024-01-18 23:55:00.0000000
1450      ||     3    || 2024-01-18 00:00:00.0000000
1230      ||     2    || 2024-01-18 00:00:00.0000000
1560      ||     4    || 2024-01-18 00:00:00.0000000
1234      ||     3    || 2024-01-18 00:05:00.0000000

需要基于该表创建新表，按TimeLogged分组，将Category转为cat1至cat5列，对应填入Wait_Time值，无数据则填0，预期结果如下：

timelogged                   || cat1    || cat2  || cat3 || cat4 || cat5
2024-01-18 23:50:00.0000000  ||  0      ||  1245 || 1230 || 0    ||   0
2024-01-18 23:55:00.0000000  ||  1260   ||  0    || 1230 || 0    ||   0
2024-01-18 00:00:00.0000000  ||  0      ||  1230 || 1450 || 1560 ||   0

我已将去重的TimeLogged插入到CAT_TIMES_CALC表，并尝试用MERGE语句更新，但未得到预期结果，尝试的代码如下：

INSERT INTO CAT_TIMES_CALC(EXTRACTTIME)
SELECT DISTINCT TimeLogged FROM CAT_EXTRACTTIMES

MERGE CAT_TIMES_CALC T
USING CAT_WAIT S ON T.EXTRACTTIME = S.timelogged
WHEN MATCHED THEN
UPDATE SET TCAT_1 =S.Category

你之前的MERGE方法无法实现需求，因为它只能单条匹配更新，无法处理同一时间点多个Category的情况。推荐用条件聚合或者PIVOT函数直接生成目标表，无需先插入再更新，更高效且准确。

方法1：条件聚合（兼容多数数据库） #

直接通过GROUP BY分组，用CASE语句判断Category值，填充对应的Wait_Time，无数据则返回0：

-- 创建新表并插入数据
CREATE TABLE CAT_TIMES_CALC (
    timelogged DATETIME2,
    cat1 INT,
    cat2 INT,
    cat3 INT,
    cat4 INT,
    cat5 INT
);

INSERT INTO CAT_TIMES_CALC(timelogged, cat1, cat2, cat3, cat4, cat5)
SELECT
    TimeLogged AS timelogged,
    MAX(CASE WHEN Category = 1 THEN Wait_Time ELSE 0 END) AS cat1,
    MAX(CASE WHEN Category = 2 THEN Wait_Time ELSE 0 END) AS cat2,
    MAX(CASE WHEN Category = 3 THEN Wait_Time ELSE 0 END) AS cat3,
    MAX(CASE WHEN Category = 4 THEN Wait_Time ELSE 0 END) AS cat4,
    MAX(CASE WHEN Category = 5 THEN Wait_Time ELSE 0 END) AS cat5
FROM CAT_EXTRACTTIMES
GROUP BY TimeLogged;

方法2：PIVOT函数（适用于SQL Server等支持的数据库） #

利用PIVOT进行行转列，再处理空值为0：

CREATE TABLE CAT_TIMES_CALC (
    timelogged DATETIME2,
    cat1 INT,
    cat2 INT,
    cat3 INT,
    cat4 INT,
    cat5 INT
);

INSERT INTO CAT_TIMES_CALC(timelogged, cat1, cat2, cat3, cat4, cat5)
SELECT
    timelogged,
    ISNULL([1], 0) AS cat1,
    ISNULL([2], 0) AS cat2,
    ISNULL([3], 0) AS cat3,
    ISNULL([4], 0) AS cat4,
    ISNULL([5], 0) AS cat5
FROM (
    SELECT TimeLogged AS timelogged, Category, Wait_Time
    FROM CAT_EXTRACTTIMES
) AS SourceTable
PIVOT (
    MAX(Wait_Time)
    FOR Category IN ([1], [2], [3], [4], [5])
) AS PivotTable;

说明： #

• 两种方法都用MAX()聚合函数，因为同一时间同一Category可能有多条记录，用MAX()可以确保每个时间-类别组合只返回一个值（符合你的预期结果格式）。
• 用ISNULL()或者CASE里的ELSE 0确保无数据的列显示0，满足需求要求。

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