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 |
|
DaveBF
Yak Posting Veteran
89 Posts |
 Posted - 2012-07-19 : 11:48:04
|
| I have a table called States which contains 3 instances of WeatherInfo. For example, one for Day1, one for Day1, and one for Day3.WeatherInfo is another table. So I have 3 foreign keys to WeatherInfo. I want to do a query, as below. But the problem is since W1.*, W2.*, and W3.* all have the same columns, I can't tell them apart in what's returned. For example, this will return 3 columns all called Temperature. Is there a way to automatically give them new column names without explicitly listing all the columns with "AS" in my Select? I hope this is clear. Thanks.SELECT s.*,W1.*,W2.*,W3.* FROM State s join WeatherInfo L1 on L1.WeatherInfo_ID = g.State_WeatherInfoDay1_ID join WeatherInfo L2 on L2.WeatherInfo_ID = g.State_WeatherInfoDay2_ID join WeatherInfo L3 on L3.WeatherInfo_ID = g.State_WeatherInfoDay3_IDFor example, this returns something like: Statename,Temperature,Rain,Temperature,Rain,Temperature,Rain. I would like to see: Statename,Temperature1,Rain1,Temperature2,Rain2,Temperature3,Rain3.There are lots more columns than Temperatue and Rain, which I why I don't want to list them all and use AS. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-07-19 : 11:53:47
|
| Don't think so - but you can generate the statement if typing is the issue.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
craigwg
Posting Yak Master
154 Posts |
Posted - 2012-07-19 : 15:33:36
|
| I would use aliases and dump the three tables into a temp table with each column aliased as needed. Then just select * from the temp table. A few extra steps but it should get the job done.Craig Greenwood |
|
|
|
|
|
|
|
|
Can Select automatically create new Column names?