Combine multiple rows into single column + Crystal Report

Question

I have a table 'BOIZ' with the following data

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

just want to combine the rows into single row based on 'bid' column. if bid = 1 then

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

if bid = 2 then

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

I have 2 ways but wanted to know how I can do it using something like CROSSAPPLY OR PIVOT OR OTHER ways

way 1 -- working fine

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

2nd way -- working fine

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;

I just want to know how can I achieve this using CROSSAPPLY OR PIVOT or by some other method.

Y'all might be wondering why I want more ways. It is because I'm working on crystal reports and the above 2 queries are not supported in the SQL expression field in the crystal report . report just crashes when using STUFF() and does not support DECLARE statements.

Answer 1

You can use a PIVOT operator with this way:

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