CrossTab based on 2 fields

[anandviru]

This is what the data looks like :

RecordNumber Complication1 Complication2 Grade-Complication1 Grade-Complication2
XX1001 surgical ortho I III
XX1002 surgical neuro I II
XX1003 neuro ortho III I
XX1004 surgical surgical II III

Desired crosstab output :

Complication GradeI GradeII GradeIII Total
surgical 2 1 1 4
ortho 1 0 1 2
neuro 1 0 1 2

Can someone help me on how to built this crosstab ? . Please let me know if you have any question on the sample data given above.

 

[IanWaterman]

You can not do this in crystal directly you will need to build a view of data or use a command

Select RecordNumber, Complication1, grade_complication1, 'Complication1' as ComplicationType
from yourtable
union all
Select RecordNumber, Complication2, grade_complication2, 'Complication2' as ComplicationType
from your table

You can now easily perform cross tab on this data set.

Ian

 

[anandviru]

Thank you so much Ian. That really works !!!