如何用SQL将同一主序列号的多行备用序列号合并为单行？

客户数据库设计不规范，产品数据包含主序列号（serial）和0至4个备用序列号（alt_serial），但serial和alt_serial都不是主键，每个备用序列号对应一条主序列号重复的行，示例表结构及数据如下：

test_table:
id | serial     | type           | alt_serial
----+------------+----------------+-----------------
0 | XL00007    | AA             | XL700001
1 | XL00007    | AB             | XL700002
2 | MARF665    | AC             | XTRA0001
3 | MARF665    | AD             | XTRA0002
4 | MARF665    | AE             | XTRA0003
5 | GLOMP12    | AF             | GLOMPX01
6 | GLOMP12    | AG             | GLOMPX02
7 | GLOMP12    | AH             | GLOMPX03
8 | SLONK15    | AI             | SLONKX01
9 | SLONK15    | AJ             | SLONKX02

需求是编写单个SQL查询（允许使用UNION），将数据扁平化处理为每个主序列号一行，把备用序列号拼接成单个字符串列，期望结果如下：

serial      | alt_serials
------------+-----------------------------------------------------
XL00007     | XL700001|XL700002
MARF665     | XTRA0001|XTRA0002|XTRA0003
GLOMP12     | GLOMPX01|GLOMPX02|GLOMPX03
SLONK15     | SLONKX01|SLONKX02

注：无修改数据/表结构权限，仅掌握基础SQL，需要简单可行的实现方案。

不同数据库的字符串聚合函数存在差异，以下是主流数据库的具体实现方式：

MySQL/MariaDB #

使用GROUP_CONCAT函数，指定分隔符为|即可：

SELECT 
    serial,
    GROUP_CONCAT(alt_serial SEPARATOR '|') AS alt_serials
FROM test_table
GROUP BY serial;

PostgreSQL #

使用STRING_AGG函数完成字符串拼接：

SELECT 
    serial,
    STRING_AGG(alt_serial, '|') AS alt_serials
FROM test_table
GROUP BY serial;

SQL Server #

2017及以上版本 #

直接使用STRING_AGG函数：

SELECT 
    serial,
    STRING_AGG(alt_serial, '|') AS alt_serials
FROM test_table
GROUP BY serial;

2016及以下版本 #

通过FOR XML PATH模拟字符串聚合：

SELECT 
    t1.serial,
    STUFF(
        (SELECT '|' + t2.alt_serial 
         FROM test_table t2 
         WHERE t2.serial = t1.serial 
         FOR XML PATH('')),
        1, 1, ''
    ) AS alt_serials
FROM test_table t1
GROUP BY t1.serial;

Oracle #

使用LISTAGG函数，可通过ORDER BY指定备用序列号的拼接顺序：

SELECT 
    serial,
    LISTAGG(alt_serial, '|') WITHIN GROUP (ORDER BY id) AS alt_serials
FROM test_table
GROUP BY serial;

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