Alternate way to perform PIVOTING without using PIVOT

Let's say we have a table as under

CREATE TABLE #FinalRates
(
ID INT PRIMARY KEY IDENTITY(1,1),
Rate VARCHAR(50),
Amount INT
)

INSERT INTO #FinalRates VALUES('100',200)
INSERT INTO #FinalRates VALUES('100',300)
INSERT INTO #FinalRates VALUES('50-80',100)
INSERT INTO #FinalRates VALUES('50-80',300)
INSERT INTO #FinalRates VALUES('30-50',500)
INSERT INTO #FinalRates VALUES('30-50',250)

SELECT *
FROM #FinalRates

DROP TABLE #FinalRates

/*
ID	Rate	Amount
---	---	------
1	100	200
2	100	300
3	50-80	100
4	50-80	300
5	30-50	500
6	30-50	250
*/

We want the output to be

Rate	Amount1	Amount2
----	------	-------
100	200	300
30-50	500	250
50-80	100	300

By using PIVOT statement introduce since SQL Server 2005, we can achieve this as under

SELECT Rate, [Amount1], [Amount2] From
(
    SELECT Rate, Amount
    , 'Amount' + CAST(ROW_NUMBER() OVER (PARTITION BY Rate ORDER BY Amount) AS VARCHAR(5)) RowVal
    FROM #FinalRates
) x
PIVOT
(
    MAX(Amount) FOR RowVal IN ([Amount1], [Amount2])
) p

The same can also be achieve without using the PIVOT as under

SELECT Rate,
       MAX(CASE WHEN id % 2 = 1 THEN Amount END) AS Amount1,
       MAX(CASE WHEN id % 2 = 0 THEN Amount END) AS Amount2
FROM #FinalRates fr
GROUP BY Rate;

Another approach could be using FOR XML PATH as shown below

;WITH Cte AS(
SELECT 
    Rate 
    ,Amounts=
            STUFF((SELECT ','+ CAST(CAST(Amount AS INT) AS VARCHAR(5))
            FROM #FinalRates T1
            WHERE T1.Rate=T2.Rate
            FOR XML PATH('')),1,1,'') 
FROM #FinalRates T2
GROUP BY T2.Rate
)

SELECT 
        Rate,
        Amount1 = PARSENAME(REPLACE(Amounts,',','.'),2),
        Amount2 = PARSENAME(REPLACE(Amounts,',','.'),1)
FROM Cte

The second one is more preferable as it is ANSI SQL and will work for any database.