target table formatting

[Simba]

I have a table like this.

Source
ID contactType Phone No.
1 Office 1234567
1 Hotline 1234567
1 Fax 1234567
1 After Hours 1234567


My requirement is like this.

Target
ID OfficePhone hotline Fax After Hours
1 1234567 1234567 1234567 1234567

From the source table, I split the columns and made a UNION ALL but still could not get it in one record.
Then I made the Group by id and made an INNER join with the rest of the columns but still could not get it in one record.

Can any one tell me the method to do this?

 

[plog]

You want a CrossTab Query. This is the SQL for what you want:

TRANSFORM First(YourTableName.Phone) AS FirstOfPhone
SELECT YourTableName.ID
FROM YourTableName
GROUP BY YourTableName.ID
PIVOT YourTableName.contactType;


Couple of notes--Change 'YourTableName' to the name of your table, I changed the phone field to 'Phone' because 'Phone No.' is an invalid field name.

 

[Simba]

Thank you plog! That worked awesome!


Here is my query.


TRANSFORM First(tblPhoneNumbers.Phone)
SELECT tblPhoneNumbers.ResourceID
FROM tblPhoneNumbers
GROUP BY tblPhoneNumbers.ResourceID
PIVOT tblPhoneNumbers.contactsType;