Alternate way to perform PIVOTING without using PIVOT

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 257
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.

Comments or Responses

Login to post response