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 |
akash.agrawalin
Starting Member
4 Posts |
Posted - 2012-11-01 : 12:41:26
|
I have 2 tables as belowTable1: PersonId FNAME LNAME1 xxx yyy2 aaa zzzTable2: ContactsContactID Id PHONENUMBER PHONETYPE1 1 12345 HOME2 1 67890 OFFICE3 2 54321 HOME4 2 09876 OFFICEI want the result for my report as below:ID FNAME LNAME HOME OFFICE1 xxx yyy 12345 678902 aaa zzz 54321 09876Can you please help me write the query to get this kind of result? It will be of great help..... |
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-11-01 : 13:15:38
|
[CODE]select p.id, p.fname, p.lname, h.phonenumber, o.phonenumberfrom Person pinner join Contacts h on p.id = h.id and h.phonetype = 'HOME'inner join Contacts o on p.id = o.id and o.phonetype = 'OFFICE'[/CODE]You may want to use outer joins to support the case where a Person does not have both types of phones. Also, a PIVOT might be a better approach, but I rarely use it so can't really speak to that.HTH=================================================We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry |
|
|
akash.agrawalin
Starting Member
4 Posts |
Posted - 2012-11-01 : 13:35:32
|
Bustaz,Thanks for your reply. But this query is giving me empty result set. |
|
|
akash.agrawalin
Starting Member
4 Posts |
Posted - 2012-11-01 : 13:58:21
|
hey,sorry for that post. Its working fine. Thanks a lot.... |
|
|
akash.agrawalin
Starting Member
4 Posts |
Posted - 2012-11-01 : 14:05:54
|
I have 1 more problem regarding this. My table2 rows might grow in future to have more phone numbers like mobile1,mobile2. So i need some dynamic sql to solve this issue.Can you please also let me know how can i achieve this? |
|
|
|
|
|