create columns from multiple rows


2007-05-18 08:31:35 PM
delphi112
Hi! I have MYTABLE with this columns:
C_PATIENT, DATE, ITEM,POINTS
where C_PATIENT is the patient's code that identifies the patient, DATE is the date that a test is applied to that patient ITEM is the item (a question) of the test and POINTS are the points the patient took that date for that item.
Each test is applied more than once to a patient in different dates, and I need to create a NEWTABLE that looks like this:
C_PATIENT, DATE1,ITEM11, DATE2,ITEM12, DATE3,ITEM13,
DATE4,ITEM14, DATE5,ITEM15
ITEM11=ITEM12= ITEM13=ITEM14= ITEM15 ='Item' it is the only item that we have to use for creating NEWTABLE
NEWTABLE has 11 columns, result of each application of the test and each row contains the patient code, the test dates and the POINTS he took in 'Item'.
The test is applied at most 5 times to each patient, some patients took the test once, others twice, other 3 times, other 4 times and others 5 times.
The question 'Item' in MYTABLE never has a null value.
Thanks for your time.