Hi,
This has to be done in SQL. I can't use Reporting tools. This query will be inputted into Informatica.
I am not a SQL expert but I am learning. I am trying to figure out how to write the query for two table in a one to many relationship.
`Table1` has the person information :
`(PersonId, FirstName, MiddleName, LastName)` and `table2` has `(PersonId, Phone, PhoneType)`.
Here is my query so far
select Table1.PERSON_ID,
Table1.FIRST_NAME,
Table1.MIDDLE_NAME,
Table1.LAST_NAME,
Table2.PHONE_NUMBER
from Table1
inner join Table2
on Table2.PERSON_ID = Table1.PERSON_ID
where Table2.PHONE_TYPE in ('BUSINESS','PERSONAL','HOME')
Here is a tables
Table1
PERSON_ID FIRST_NAME MIDDLE_NAME LAST_NAME
1 John Carter Jones
Table2
PERSON_ID PHONE_NUMBER PHONE_TYPE
1 111-111-1111 HOME
1 111-111-1112 PERSONAL
1 111-111-1113 BUSINESS
From my query I get
1 John Carter Jones 111-111-1111
1 John Carter Jones 111-111-1112
1 John Carter Jones 111-111-1113
I would like to achieve the following result
1 John Carter Jones 111-111-1111 111-111-1112 111-111-1113
The 3 set of phone numbers will come back as 3 separate columns, like HomePhone, BusPhone, CellPhone
Any help is greatly appreciated.