Multiple join of tables while updating the table

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 2005 Forums

Other SQL Server Topics (2005)

Multiple join of tables while updating the table

Author

Topic

vikas.wac
Starting Member

1 Post

Posted - 2011-07-06 : 18:33:46

Hi,

I am stuck in a SQL query. I have 2 tables say t1 and t2.
I have to join these two tables using a key. This is very simple. I have done that.

t1 structure :
key, lat, long
435, 29, -56

t2 structure :
key, freqa, freqb
435, 3000, 4000
435, 2000, 1100
435, 8000, 7500

t2 has multiple number of records for the same key

simple inner join is giving me this:

key, lat, long, freqa, freqb
435, 29, -56 , 3000, 4000
435, 29, -56 , 2000, 1100
435, 29, -56 , 8000, 7500

my desired output should have only unnique records and i want to update the table with additional columns from the same key records.

my desired output structure :
key, lat, long, freqa, freqb ,freqa, freqb, freqa, freqb
435, 29, -56 , 3000, 4000 ,2000, 1100 8000, 7500

Could you please help me out with this query.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-06 : 19:31:07

with cte as
(select *, seq = row_number() over (partition by key order by freqa) from t2)
select t1.*, t2.freqa, t2.freqb, t3.freqa, t3.freqb, t4.freqa, t4.freqb
from t1
join cte t2
on t1.key = t2.key and t2.seq = 1
join cte t3
on t1.key = t3.key and t3.seq = 2
join cte t4
on t1.key = t4.key and t4.seq = 3

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources