如何用PIVOT（无聚合函数）将Name作为列名并按日保留单条日期？

现有SQL查询 #

SELECT a.Name, b.Date FROM
Table1 a 
JOIN Table2 b on a.deviceID=b.id
where a.date > '2022-10-01'

原查询返回结果 #

Name Date
A1   '2022-10-01 12:13'
A2   '2022-10-02 14:15'
A2   '2022-10-02 15:16'
A5   '2022-10-03 16:19' 

期望结果格式 #

A1                 A2                        A5 
'2022-10-01 12:13' '2022-10-02 14:15'       '2022-10-03 16:19' 
                    '2022-10-02 15:16'

解答 #

可以用PIVOT实现，但需要先给每个Name分组内的记录添加行号，用来区分同一设备的多条日期记录，这样PIVOT才能将同一设备的不同行数据映射到结果的不同行中。

具体实现步骤： #

1. 先给原查询结果添加行号，按Name分组、Date排序生成行标识：

SELECT 
    Name,
    Date,
    ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Date) AS rn
FROM (
    SELECT a.Name, b.Date 
    FROM Table1 a 
    JOIN Table2 b on a.deviceID=b.id
    where a.date > '2022-10-01'
) t

2. 基于带行号的结果执行PIVOT，以rn为行标识，Name为列，Date为值：

SELECT 
    [A1], [A2], [A5]
FROM (
    SELECT 
        Name,
        Date,
        ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Date) AS rn
    FROM (
        SELECT a.Name, b.Date 
        FROM Table1 a 
        JOIN Table2 b on a.deviceID=b.id
        where a.date > '2022-10-01'
    ) t
) src
PIVOT (
    MAX(Date) FOR Name IN ([A1], [A2], [A5])
) pvt

补充说明： #

• ROW_NUMBER()生成的rn是核心，确保同一Name的多条记录能分到结果的不同行；
• MAX(Date)是PIVOT要求的聚合函数，因为同一rn+Name组合只有一条记录，用MAX/MIN效果一致；
• 如果Name的取值不固定，需要用动态SQL生成PIVOT的列列表。

针对“每日仅保留一条日期记录”的需求，可以先在子查询里按Name和日期的天维度分组，取当日最早/最晚的日期，再进行后续的行号添加和PIVOT操作，示例如下：

SELECT 
    [A1], [A2], [A5]
FROM (
    SELECT 
        Name,
        Date,
        ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Date) AS rn
    FROM (
        SELECT 
            Name,
            MAX(Date) AS Date -- 取当日最晚时间，换MIN则取最早
        FROM (
            SELECT a.Name, b.Date 
            FROM Table1 a 
            JOIN Table2 b on a.deviceID=b.id
            where a.date > '2022-10-01'
        ) t
        GROUP BY Name, CAST(Date AS DATE)
    ) t2
) src
PIVOT (
    MAX(Date) FOR Name IN ([A1], [A2], [A5])
) pvt

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