如何将同一BookingId的Booking Created和Booking Cancelled备注合并？

现有两张表Bookings和BookingNote：

• Bookings表包含BookingId及其他预订相关字段；
• 每当预订信息更新时，会向BookingNote表添加一条备注。

BookingNote表示例数据：

当前执行的SQL语句（注：原语句中Notes应为BookingNote的笔误，已修正）：

Select B.BookingId ,
            Case 
            When N.Note = 'Booking Created'  Then N.Note 
            When N.Note = 'Booking Cancelled' Then N.Note 
    End
    
    
    From    Bookings as B
        Join    BookingNote as N
     On B.BookingId = N.BookingId
        Where   N.Note = 'Booking Created'
        Or  N.Note = 'Booking Cancelled'

得到的查询结果：

需求：当某个BookingId同时存在Booking Created和Booking Cancelled备注时，将这两个备注合并为一条记录，期望结果如下：

核心思路是分组聚合：先过滤出目标备注，再按BookingId分组，将多个Note值拼接为单个字符串。以下是主流数据库的实现方式：

1. MySQL/MariaDB #

使用GROUP_CONCAT函数：

SELECT 
    B.BookingId,
    GROUP_CONCAT(N.Note SEPARATOR ' , ') AS Note
FROM Bookings AS B
JOIN BookingNote AS N ON B.BookingId = N.BookingId
WHERE N.Note IN ('Booking Created', 'Booking Cancelled')
GROUP BY B.BookingId
-- 可选：指定拼接顺序，比如先显示取消记录
-- GROUP_CONCAT(N.Note ORDER BY N.Note DESC SEPARATOR ' , ') AS Note

2. SQL Server 2017+/Azure SQL #

使用STRING_AGG函数：

SELECT 
    B.BookingId,
    STRING_AGG(N.Note, ' , ') AS Note
FROM Bookings AS B
JOIN BookingNote AS N ON B.BookingId = N.BookingId
WHERE N.Note IN ('Booking Created', 'Booking Cancelled')
GROUP BY B.BookingId
-- 可选：指定拼接顺序
-- STRING_AGG(N.Note, ' , ') WITHIN GROUP (ORDER BY N.Note DESC) AS Note

3. Oracle 11g+ #

使用LISTAGG函数：

SELECT 
    B.BookingId,
    LISTAGG(N.Note, ' , ') WITHIN GROUP (ORDER BY N.Note) AS Note
FROM Bookings AS B
JOIN BookingNote AS N ON B.BookingId = N.BookingId
WHERE N.Note IN ('Booking Created', 'Booking Cancelled')
GROUP BY B.BookingId

4. PostgreSQL #

使用STRING_AGG函数：

SELECT 
    B.BookingId,
    STRING_AGG(N.Note, ' , ') AS Note
FROM Bookings AS B
JOIN BookingNote AS N ON B.BookingId = N.BookingId
WHERE N.Note IN ('Booking Created', 'Booking Cancelled')
GROUP BY B.BookingId
-- 可选：指定拼接顺序
-- STRING_AGG(N.Note, ' , ') ORDER BY N.Note DESC AS Note

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