Crosstab Qry with dynamic field names

Hi,

I am trying to create a stored procedures (SQL 7.0), to provide data
in
a crosstab format.
(I'm using Crystal Reports 8.5, but the Crosstab capabilities are
terrible, so I have to do as much as possible on the SQL side)

I have a table [Occurrences] with the following fields:
Year (int)
Month (int)
Occurs (int)
Claims (int)

I need a query to give me the following format:
Acct_Month 2001 2002 2003
Occurs Claims Occurs Claims Occurs Claims

January 120 180 132 196 110 140
February 154 210 165 202 144 178
March etc..
....

Catch! I need the Year field name to be the contents of the field
Year in the Table (2001, 2002, 2003...). Not the usual Year_1, Year_2
approach.

I got the month name ok...
Acct_Month = DATENAME(month, Convert(Varchar(2), Month) + '/01/'+
Convert(Char(4),Year))

Is it possible to do this easely, without the use of cursors?

Any help would be much appreciated.

Luis PintoLuis (luispinto@.att.net) writes:
> I need a query to give me the following format:
> Acct_Month 2001 2002 2003
> Occurs Claims Occurs Claims Occurs Claims
> January 120 180 132 196 110 140
> February 154 210 165 202 144 178
> March etc..
> ...
> Catch! I need the Year field name to be the contents of the field
> Year in the Table (2001, 2002, 2003...). Not the usual Year_1, Year_2
> approach.
> I got the month name ok...
> Acct_Month = DATENAME(month, Convert(Varchar(2), Month) + '/01/'+
> Convert(Char(4),Year))
> Is it possible to do this easely, without the use of cursors?

It takes a whole of dynamic SQL to get there.

You should probably investigate what RAC can do for you, see
http://www.rac4sql.net/.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp