Crystal Report中按bid合并多行数据：求CROSSAPPLY/PIVOT替代方案

我有一张名为BOIZ的表，数据如下：

+-----+------+
| bid | nums |
+=====+======+
| 1   | 101  |
+-----+------+
| 1   | 103  |
+-----+------+
| 2   | 102  |
+-----+------+
| 1   | 105  |
+-----+------+
| 2   | 101  |
+-----+------+
| 2   | 115  |
+-----+------+
| 2   | 118  |
+-----+------+
| 2   | 21   |
+-----+------+

需要基于bid列将多行合并为单行：

• 当bid=1时，结果为：

+---------------------+
| 101st, 103rd, 105th |
+---------------------+

• 当bid=2时，结果为：

+----------------------------------+
| 102nd, 101st, 115th, 118th, 21st |
+----------------------------------+

已有的可行方法 #

方法1（可正常运行） #

select STUFF((select ', ' +t1.OrdinalNumber from
  (select BZ.bid,Cast(
  BZ.nums as VARCHAR(15)) + 
   CASE WHEN BZ.nums % 100 IN (11,12,13) THEN 'th' 
   WHEN BZ.nums % 10 = 1 THEN 'st' 
   WHEN BZ.nums % 10 = 2 THEN 'nd' 
   WHEN BZ.nums % 10 = 3 THEN 'rd' 
   ELSE 'th' 
   END AS OrdinalNumber from BOIZ BZ 
where BZ.bid = 2
) as t1
FOR XML PATH('')
    ), 1, 1, '') AS BOXED

方法2（可正常运行） #

Declare @val Varchar(MAX); 
Select @val = COALESCE(@val + ', ' + OrdinalNumber, OrdinalNumber)
        From(select BZ.bid,Cast(
  BZ.nums as VARCHAR(15)) + 
   CASE WHEN BZ.nums % 100 IN (11,12,13) THEN 'th' 
   WHEN BZ.nums % 10 = 1 THEN 'st' 
   WHEN BZ.nums % 10 = 2 THEN 'nd' 
   WHEN BZ.nums % 10 = 3 THEN 'rd' 
   ELSE 'th' 
   END AS OrdinalNumber from BOIZ BZ 
where BZ.bid = 2)
 as t1 Select @val;

遇到的限制 #

由于使用Crystal Report，上述两种方法在其SQL表达式字段中不被支持：使用STUFF()会导致报表崩溃，且不支持DECLARE语句。

使用CROSS APPLY结合XML拼接（替代STUFF） #

这种写法避开了STUFF()和变量声明，兼容Crystal Report的SQL表达式限制：

SELECT 
    b.bid,
    SUBSTRING(x.OrdinalList, 3, LEN(x.OrdinalList)) AS BOXED
FROM 
    (SELECT DISTINCT bid FROM BOIZ) b
CROSS APPLY 
    (SELECT ', ' + 
        CAST(nums AS VARCHAR(15)) + 
        CASE 
            WHEN nums % 100 IN (11,12,13) THEN 'th' 
            WHEN nums % 10 = 1 THEN 'st' 
            WHEN nums % 10 = 2 THEN 'nd' 
            WHEN nums % 10 = 3 THEN 'rd' 
            ELSE 'th' 
        END 
     FROM BOIZ 
     WHERE bid = b.bid
     FOR XML PATH('')) x(OrdinalList)

逻辑说明：

1. 先获取所有唯一的bid值；
2. 通过CROSS APPLY对每个bid对应的nums生成带序数后缀的字符串，再用FOR XML PATH('')拼接成以, 开头的完整字符串；
3. 用SUBSTRING()从第3个字符开始截取，去掉开头多余的, ，得到最终合并结果。

单bid查询简化版 #

如果只需要查询特定bid（比如bid=2），可以简化为：

SELECT 
    SUBSTRING(x.OrdinalList, 3, LEN(x.OrdinalList)) AS BOXED
FROM 
    (SELECT ', ' + 
        CAST(nums AS VARCHAR(15)) + 
        CASE 
            WHEN nums % 100 IN (11,12,13) THEN 'th' 
            WHEN nums % 10 = 1 THEN 'st' 
            WHEN nums % 10 = 2 THEN 'nd' 
            WHEN nums % 10 = 3 THEN 'rd' 
            ELSE 'th' 
        END 
     FROM BOIZ 
     WHERE bid = 2
     FOR XML PATH('')) x(OrdinalList)

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