I'm having trouble compacting some data.

This is an example of the data I have:

John Smith | Buyer |_____|______________|_______________
John Smith | _____| Seller |______________|_______________
John Smith | _____|_____| johns@email.com | _____________
John Smith | _____|_____|______________|408-555-1212
Mary Jones |_____| Seller |______________|_______________
Mary Jones |_____|_____|maryj@email.com |_______________
Mary Jones |_____|_____|______________|408-123-9876

Or (see second SQL Script for details):

John Smith | Buyer | johns@email.com
John Smith | Buyer | 408-555-1212
John Smith | Seller | johns@email.com
John Smith | Seller | 408-555-1212
Mary Jones | Seller | maryj@email.com
Mary Jones | Seller | 408-123-9876

Here's what I want it to look like in Crystal

John Smith | Buyer | Seller | johns@email.com | 408-555-1212
Mary Jones | _____| Seller | maryj@email.com | 408-123-9876

The reason for the broken data is because of how the tables are setup in the database.

For instance, the name is in the Person table.  The e-mail addresses and phone numbers are in the Contact table, but there's also a ContactType table that contains the types (labels) of contact data ("e-mail address", "mobile phone", "home phone", "office", etc.)

The SQL script to gather the data is something like this:

First Script (returns first data set - Let's ignore the Buyer / Seller person type for simplicity):

SELECT Person.Name
, CASE WHEN ContactType.Description = 'E-Mail' THEN Contact.Data ELSE '' END  'E-mail'
, CASE WHEN ContactType.Description = 'Mobile' THEN Contact.Data ELSE '' END  'Mobile'
FROM Person
Left Join Contact on ( Person.PK = Contact.pkPerson )
Left Join ContactType on ( Contact.pkContactType = ContactType.PK )

Second script (returns second data set):
SELECT Person.Name, PersonType.Description, Contact.Data
FROM Person
Left Join Contact on ( Person.PK = Contact.pkPerson )
Left Join ContactType on ( Contact.pkContactType = ContactType.PK )
Left Join PersonType on ( PersonType.PK = Person.fkPersonType )

I know there's a way to compact the first data set using grouping and suppression, but I just can't for the life of me remember how it's done.

Thanks in advance.


Edited by Chris M - 09 Apr 2013 at 5:14pm