I have a table in a VFP 6.0 database that looks like this:
stcode C(3)
cyear C(4)
cweek C(2)
clob C(2)
ydgrsprm N(10,0)
ydnetprm N(10,0)
...
etc
...

The field stcode is a state code foreign key and the clob field is a line-of-business foreign key.

The result table I want should look like this:
stcode C(3)
clob C(2)
cweek C(2)
y0_ydgrsprm N(10,0)
y1_ydgrsprm N(10,0)
y2_ydgrsprm N(10,0)
y0_ydnetprm N(10,0)
y1_ydnetprm N(10,0)
y2_ydnetprm N(10,0)

The y0_ means current year,y1_ means first previous year and y2_means second previous year; i.e. 1998,1997,1996.

The state code and week are determined by variables selected by the user.

I need one SELECT-SQL statement that will give me this information. We will be using in an ASP; so, it has to be compatible with SQL Server/ADO. Is there a way to do this or will I have to collect 3 recordsets and dump them into an array that an HTML table can display?

Thanks,

Dan Rhymes