SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query - Row to column?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wypwong
Starting Member

9 Posts

Posted - 10/09/2012 :  08:15:08  Show Profile  Reply with Quote
Hi all

I have this table
MonthBegDate Rate
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
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

Hope someone can help me here. Thanks.

Phyllis

lazerath
Constraint Violating Yak Guru

USA
343 Posts

Posted - 10/09/2012 :  18:34:12  Show Profile  Reply with Quote
Look into the PIVOT operator.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/09/2012 :  21:32:55  Show Profile  Reply with Quote
are there always four values per MonthBegDate?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

nandhujob88
Starting Member

India
4 Posts

Posted - 10/10/2012 :  06:35:26  Show Profile  Reply with Quote
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
Go to Top of Page

nandhujob88
Starting Member

India
4 Posts

Posted - 10/10/2012 :  06:38:06  Show Profile  Reply with Quote
let me know the feedback please

Nandhu - Software Analyst
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/11/2012 :  00:19:37  Show Profile  Reply with Quote
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


this will put all values as comma separated list in same field which is not OP want as per sample output


;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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

wypwong
Starting Member

9 Posts

Posted - 10/11/2012 :  05:50:15  Show Profile  Reply with Quote
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.
Go to Top of Page

wypwong
Starting Member

9 Posts

Posted - 10/11/2012 :  05:50:15  Show Profile  Reply with Quote
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.
Go to Top of Page

wypwong
Starting Member

9 Posts

Posted - 10/11/2012 :  05:50:16  Show Profile  Reply with Quote
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.
Go to Top of Page

wypwong
Starting Member

9 Posts

Posted - 10/11/2012 :  06:39:40  Show Profile  Reply with Quote
Hi Nandhu

Thanks for showing me how to use xml path, I didn't know this function.
I can see where you are getting to, but I do need information in columns rather than a long text.

P
Go to Top of Page

wypwong
Starting Member

9 Posts

Posted - 10/11/2012 :  06:39:41  Show Profile  Reply with Quote
Hi Nandhu

Thanks for showing me how to use xml path, I didn't know this function.
I can see where you are getting to, but I do need information in columns rather than a long text.

P
Go to Top of Page

wypwong
Starting Member

9 Posts

Posted - 10/11/2012 :  06:39:41  Show Profile  Reply with Quote
Hi Nandhu

Thanks for showing me how to use xml path, I didn't know this function.
I can see where you are getting to, but I do need information in columns rather than a long text.

P
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/11/2012 :  15:49:52  Show Profile  Reply with Quote
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.


sorry didnt understand why you cant extend my suggestion to fit these additional month values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

nandhujob88
Starting Member

India
4 Posts

Posted - 10/12/2012 :  02:36:46  Show Profile  Reply with Quote
Hi wypwong,

Your Always Welocme..




Nandhu - Software Analyst
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000