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.
| Author |
Topic |
|
zwheeler
Starting Member
25 Posts |
 Posted - 2012-02-07 : 17:06:08
|
| hiI have a query and it works when i hard code the column name in the select statement of the openquery.Select cast([pk_id] as int) fk_id,[1978q1] intRatefrom OpenQuery(Moodys_Linked,'Select * from [Sheet1$]')output examplefk_id intRate1 1.252 0.93 0.606however, i have to get the intRate for each quarter for 57 years and i am trying to build the column name for the intRate ([Year + Quarter])so I would have [1964Q1], [1964Q2],[1964Q3],[1964Q4],[1965Q1],.......... (NOTE: In the spreadsheet these represent the column names.I have tried to to build a while loop to create these column names dynamically[code="sql"]declare @counter intdeclare @count1 intset @counter = 1964set @count1 = 1/** Insert data from moodys extract **/ while @count1 < 5beginwhile @counter < 2022beginSelect cast([pk_id] as int) fk_id,cast ('[' + cast(@counter as varchar) + 'Q' + cast(@count1 as varchar) + ']' as varchar ) intRatefrom OpenQuery(Moodys_Linked,'Select * from [Sheet1$]')set @counter = @counter + 1endset @count1 = @count1 + 1set @counter = 1964end[/code/however, instead of creating the name in the spreadsheet and returning the values it is simply creating a varchar value for each recordi.e.fk_id intRate1 1978Q12 1978Q23 1978Q34 1978Q45 1979Q1...............Is it possible to create column name of a table or excel spreadsheet from scratch (i.e. dynamically)orwhat am i doing wrongany help is greatly appreciated.Thanks in advance |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
Help Writing query