Crosstab to convert rows to columns

[foxtrot123]

I have data like:

SubjectID  VisitID VisitNum VisitDate VisitDetail Rspns
1           5       1       2/10/2001 Fever_1      Yes
1           5       1       2/10/2001 Age_1        12
2           7       1       5/08/2010 Fever_1      No
2           7       1       5/08/2010 Age_1        18
2           8       2       9/30/2010 Fever_2      Yes
2           8       2       9/30/2010 Age_2        18
etc.

I need to convert it to a non-normalized wide format, like this:

SubjectID  VisitDate_1 Fever_1  Age_1 VisitDate_2 Fever_2  Age_2
1          2/10/2001   Yes      12
2          5/08/2010   No       18    9/30/2010   Yes      18
etc.

If I didn't have to worry about the VisitDate, a simple crosstab would do the trick:

TRANSFORM First(MyTable.Rspns) AS FirstOfRspns
SELECT MyTable.PatientID
FROM MyTable
GROUP BY MyTable.PatientID
PIVOT MyTable.VisitDetail;

But that VisitDate is tripping me up. Any suggestions on how to deal with the visit date?

Thank you.

 

[plog]

You can't. A crosstab only allows you to convert 1 column of data into column headings. To get your data like you want it, you would have to export that initial dataset to Excel and then do a pivot table: http://office.microsoft.com/en-us/excel-help/pivottable-reports-101-HA001034632.aspx