Transpose rows/cols of a result set?

[pardey]

Hi,

I have a result set returned from a stored procedure that has always has the same no. of rows and colummns. I use this stored procedure for two reports, but one of them needs the results transposed.

For example

+---------+-------+-----+-----+
|  Band   | Count | Avg | Min |
+---------+-------+-----+-----+
| 0 - 30  |  10   | 5   |  0  |
| 30 - 60 |  15   | 35  |  32 |
| 60 - 90 |  12   | 64  |  63 |
| 90+     |   5   | 109 |  94 |
+---------+-------+-----+-----+
| Totals  |  xx   | xx  |  xx |
+---------+-------+-----+-----+

and I need to display this as

+---------+--------+---------+---------+-----+--------+
|  Band   | 0 - 30 | 30 - 60 | 60 - 90 | 90+ | Totals |
+---------+--------+---------+---------+-----+--------+
|  Count  |   10   |   15    |    35   |  5  | xxx    |
|  Avg    |   5    |   35    |    64   | 109 | xxx    |
|  Min    |   0    |   32    |    63   |  94 | xxx    |
+---------+--------+---------+---------+-----+--------+

I thought I would be able to use the cross-tab, but it doesn't seem to be able to manage it. The formatting is all messed up and it produces unintelligible results with complex nesting. It seems to work fine if there is only one column and row to be transposed, but not for the situation I've described above.

Any ideas on how to do what I require, or even if it is possible?

Any help would be much appreciated.

 

[GJParker]

does your stored procedure return a complete list of records by band or just the summaries i.e. always 4 records ?

what version of CR are you using ?

what database ?

Gary Parker
Systems Support Analyst
Manchester, England

 

[pardey]

The stored procedure returns just the summaries.

We're using Sybase 11.9 and Crystal Reports 8.0.1.0

 

[synapsevampire]

It seems a crosstab is fine, place the Band in the Column Header, and the 3 values in the summarized fields.

-k

 

[pardey]

The solution proposed by synapsevampire produces the following table. It is close, but I need the row labels to be in place otherwise the numbers are meaningless.

+---------+--------+---------+---------+-----+--------+
|  Band   | 0 - 30 | 30 - 60 | 60 - 90 | 90+ | Totals |
+---------+--------+---------+---------+-----+--------+
| Total   |   10   |   15    |    35   |  5  | xxx    |
|         |   5    |   35    |    64   | 109 | xxx    |
|         |   0    |   32    |    63   |  94 | xxx    |
+---------+--------+---------+---------+-----+--------+

 

[pardey]

Sorry, I just realised that I can edit the row label, so that one is sorted.

Is it possible to adjust the row and column widths?

 

[pardey]

Yep, got that one sorted too... Column/Row heights and widths are adjusted by changing the widths of the headings.