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 2008 Forums
 Transact-SQL (2008)
 How to get desired result using simple sql query

Author  Topic 

akash.agrawalin
Starting Member

4 Posts

Posted - 2012-11-01 : 12:41:26
I have 2 tables as below

Table1: Person

Id FNAME LNAME
1 xxx yyy
2 aaa zzz

Table2: Contacts

ContactID Id PHONENUMBER PHONETYPE
1 1 12345 HOME
2 1 67890 OFFICE
3 2 54321 HOME
4 2 09876 OFFICE

I want the result for my report as below:

ID FNAME LNAME HOME OFFICE
1 xxx yyy 12345 67890
2 aaa zzz 54321 09876

Can 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.phonenumber
from Person p
inner 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
Go to Top of Page

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.
Go to Top of Page

akash.agrawalin
Starting Member

4 Posts

Posted - 2012-11-01 : 13:58:21
hey,sorry for that post. Its working fine. Thanks a lot....
Go to Top of Page

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?
Go to Top of Page
   

- Advertisement -