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 |
|
dvirdc
Starting Member
3 Posts |
 Posted - 2011-07-06 : 16:48:02
|
| hey guysim trying to figure how to handle this:i have this table|id | string1 |string2 | string3 ||------------------------------||121 |bla11 |bla 12 |bla13 ||------------------------------||121 |bla21 |bla 22 |bla23 ||------------------------------||121 |bla31 |bla 32 |bla33 |-------------------------------and this:|id | string4 |string5 | string6 ||------------------------------||121 |bla44 |bla 45 |bla46 ||------------------------------||133 |bla36 |bla 46 |bla56 ||------------------------------||155 |blan5 |blaf 55 |blak3 |-------------------------------i'd like to join on ID as you can guess. now i'd also like to ouput a single row that has all fields togethermean:|id | string4 |string5 | string6 | string1 |string1 | string1 ||--------------------------------------------------------|121 |bla44 |bla 45 |bla46 | |bla11 |bla21 |bla31 |---------------------------------------------------------yes i know it might be difficult or impossible using SQL. if anyone's got the answer, please let me know. thanks. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-06 : 18:50:32
|
| What you have there isn't difficult but what about string2, string3.Are there always 3 rows in tbl1 for each id (if not ten left join)?with cte as(select id, string1, seq=row_number() over (partition by id order by string1 from tbl1)select a.id, a.string4, a.string5, a.string6, b.string1, c.string1, d.string1from tbl2 aleft join cte bon b.id = a.id and b.seq = 1left join cte con c.id = a.id and c.seq = 2left join cte don d.id = a.id and d.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. |
 |
|
|
dvirdc
Starting Member
3 Posts |
Posted - 2011-07-06 : 20:03:54
|
| hey mateusually tbl1 has 6 rows with same id.your code looks intersting. will you be kind to explain some?thanks |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-06 : 20:08:13
|
| The cte adds a sequence number to each row for an id.The join then uses that id to join to each row.In effect it is generating a table for each occurance of the id and joining to it. So for 3 IDs it creates 3 tables.For 6 rows just add another 3 join statements.I missed the join keyword copy but I'll add that now - I've also changed it to left join in case not all have the same number of occurances..==========================================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. |
|
|
|
dvirdc
Starting Member
3 Posts |
Posted - 2011-07-06 : 20:12:36
|
| i guess i must add e,f and g tables as well? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-06 : 20:19:50
|
| Yep. Maybe call them t1, t2, ... instead.How are you using this - it's a bit odd.Maybe you could output a csv string as inhttp://www.nigelrivett.net/SQLTsql/CSVStringSQL.html==========================================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. |
|
|
|
|
|
|
|
|
join few table