quote:

---

Originally posted by nandhujob88

Hi
Please find below code for your problem

Create Table #Temp(
MonthBegDate int,Rate float)

Insert Into #Temp
Select 20121, 1.56 Union All
Select 20122, 1.60 Union All
Select 20123 ,1.62 Union All
Select 20124 ,1.60

Select * From #Temp

Select MonthBegDate ,
(
Select Stuff (
(Select ','+Cast(Rate AS Varchar(100)) From #Temp For Xml Path('')),
1,
1,
'')
)
From #Temp t

Drop Table #Temp




Nandhu - Software Analyst

---

;With MonthlyRates
AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY monthBegDate) AS Seq,*
FROM table
)

SELECT *
FROM
(
SELECT MonthBegDate,Seq,Rate
FROM (SELECT Seq,Rate FROM MonthlyRates) t
CROSS JOIN (SELECT MonthBegDate FROM MonthlyRates) m
)m
PIVOT(MAX(Rate) FOR Seq IN ([1],[2],[3],[4]))n

quote:

---

Originally posted by wypwong

Wow... Visakhm, it's magic. Thank you so much.

One thing I didn't mention in my first message, my currency table is a lot more complex, I have different currencies and a lot more years.

MonthBegDate Rate
20111 1.40
20112 1.38
...
201112 1.8
20121 1.56
20122 1.60
20123 1.62
20124 1.60
....

and I want it to convert the table to look like this
MonthBegDate Mth1Rate Mth2Rate Mth3Rate Mth4Rate
20111 1.40 1.38 ...
20112 1.40 1.38 ...
...
201112 1.4 1.38 ..... 1.8 (in Mth12Rate)
20121 1.56 1.60 1.62 1.60
20122 1.56 1.60 1.62 1.60
20123 1.56 1.60 1.62 1.60
20124 1.56 1.60 1.62 1.60


I have asked my colleague about it, he has used cursor to produce that. But I wonder whether there is other methods.

---