将多行合并为单列+ Crystal Report

Question

我有一个表'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 |
+----------------------------------+

我有两种方法，但我想知道如何使用交叉应用或PIVOT或其他方法

方式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

第二种方式——工作良好

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;

我只是想知道如何使用CROSSAPPLY或PIVOT或其他方法实现这一点。

你们可能想知道为什么我想要更多的方法。这是因为我正在研究晶体报告，并且在晶体报告中的SQL表达式字段中不支持上述2个查询。报告只在使用STUFF()时崩溃，不支持DECLARE语句。

Answer 1

可以这样使用PIVOT操作符:

SELECT bid, 
CAST([1] AS VARCHAR(MAX)) + COALESCE(', ' + CAST([2] AS VARCHAR(MAX)), '') +
COALESCE(', ' + CAST([3] AS VARCHAR(MAX)), '') + COALESCE(', ' + CAST([4] AS VARCHAR(MAX)), '') +
COALESCE(', ' + CAST([5] AS VARCHAR(MAX)), '') + COALESCE(', ' + CAST([6] AS VARCHAR(MAX)), '') +
COALESCE(', ' + CAST([7] AS VARCHAR(MAX)), '') + COALESCE(', ' + CAST([8] AS VARCHAR(MAX)), '') AS BOXED
FROM (
SELECT bid, 
ROW_NUMBER() OVER (PARTITION BY bid ORDER BY nums) AS rn, 
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 AS OrdinalNumber
FROM BOIZ
) AS t
PIVOT (
MAX(OrdinalNumber)
FOR rn IN ([1],[2],[3],[4],[5],[6],[7],[8])
) AS p