Displaying rows as columns

[marckssg]

Hi,

I'm using Crystal 2008 off an Oracle database and looking to take data currently displayed in rows and show them in individual columns in a group header/footer.

So my data currently looks like;
Col 1 Col 2
Joe Bloggs Rugby
Joe Bloggs Football
Joe Bloggs Cricket
Joe Bloggs Hockey
Fred Smith Volleyball
Fred Smith Futsal
Fred Smith Rugby
Jane Stevens Netball
Jane Stevens Football


I'd like it to display;
Col 1 Col2 Col3 Col4 Col5
Joe Bloggs Rugby Football Cricket Hockey
Fred Smith Volleyball Futsal Rugby [NULL]
Jane Stevens Netball Football [NULL] [Null]

I initially tried a cross tab with the test data which worked ok, but on the live database there are 400k people and there will typically be between 2 and 3 columns per person.

Having a total mental block now on how best to get it done, thats lead me to group them by person as a variable so they display;

Col 1 Col 2
Joe Bloggs Rugby, Football, Cricket, Hockey

Then I was going to try and split them out using an array but can't get it quite right!

Any suggestion most welcome.

Thanks.

 

[hilfy]

Is there a maximum number of entries per person? Are the entries static or can they change? Which version of Oracle are you using?

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[marckssg]

Hi,

Typically there will be 2 or 3 per person, I've not seen any examples with more than 3, so I'll be taking that as the maximum likely number. Entries can change against each person so there not static.

Its Oracle 10g, but other than knowing its an oracle database I've no means of doing anything database side that I can't pass over via Crystal, so that would limit me to SQL Commands or Expressions.

Cheers

Marc

 

[marckssg]

In the end grouped them up using a formula then split them out into columns using this formula 3 times;

if ubound(split({@Sport Group},",")) >= 1 then
split(Sport Group,",")[1]

With @Sport Group being a String Var that collects them all into one field in the group footer displayed as "Football, Cricket, Netball".

I'm sure its not the most elegant solution but its done the job for now!

Cheers

Marc