As example of Pivoting using FOR XML PATH

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 207
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 the below TSQL Script we can achieve so

;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

Comments or Responses

Login to post response