Query - Row to column? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query - Row to column?

Author

Topic

wypwong
Starting Member

9 Posts

Posted - 2012-10-09 : 08:15:08

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

343 Posts

Posted - 2012-10-09 : 18:34:12

Look into the PIVOT operator.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-09 : 21:32:55

are there always four values per MonthBegDate?

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

nandhujob88
Starting Member

4 Posts

Posted - 2012-10-10 : 06:35:26

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

nandhujob88
Starting Member

4 Posts

Posted - 2012-10-10 : 06:38:06

let me know the feedback please

Nandhu - Software Analyst

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-11 : 00:19:37

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/

wypwong
Starting Member

9 Posts

Posted - 2012-10-11 : 05:50:15

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.

wypwong
Starting Member

9 Posts

Posted - 2012-10-11 : 05:50:15

wypwong
Starting Member

9 Posts

Posted - 2012-10-11 : 05:50:16

wypwong
Starting Member

9 Posts

Posted - 2012-10-11 : 06:39:40

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

wypwong
Starting Member

9 Posts

Posted - 2012-10-11 : 06:39:41

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

wypwong
Starting Member

9 Posts

Posted - 2012-10-11 : 06:39:41

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-11 : 15:49:52

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/

nandhujob88
Starting Member

4 Posts

Posted - 2012-10-12 : 02:36:46

Hi wypwong,

Your Always Welocme..

Nandhu - Software Analyst

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources