report structure/pivot query

Hi

I'm having trouble with the structure of my report. Consultations with various SQL forums have informed me that I should not try to manipulate my data in the query but do it in Crystal.

The format I want is:

BookID, Author1, Author2, Author3, Author4, BookTitle, PublisherName, YearOfPublication, ISBN

The dataset I have from my query is:

BookID, Author1, BookTitle, PublisherName, YearOfPublication, ISBN

BookID, Author2, BookTitle, PublisherName, YearOfPublication, ISBN

BookID, Author3, BookTitle, PublisherName, YearOfPublication, ISBN

BookID, Author4, BookTitle, PublisherName, YearOfPublication, ISBN

I need to group the data on the BookID so that the book details only show once for each instance but I can't find a way to concatenate all the author details and group it properly within the book. At the moment, the group is based on the BookID and the group header section contains BookID and BookTitle. The details section contains a formula field that concatenates the surname, forename and initials of an individual author. The Group Footer section contains all the other book details fields. This means that the output looks like this:

BookID, BookTitle
Author1
Author2
Author3
Author4
PublisherName, YearOfPublication, ISBN

Please can anyone help?