Hi Alejandro!

I got confused with ITEM and POINTS, not quite understanding which of them meant what. Here's a possible solution when you don't take ITEM into account and no person takes two tests on the same day:

select m1.c_patient, m1.MyDate, m1.MyPoints, m2.MyDate, m2.MyPoints, m3.MyDate, m3.MyPoints,
m4.MyDate, m4.MyPoints, m5.MyDate, m5.MyPoints
from MyTable m1
left join MyTable m2 on m1.c_patient = m2.c_patient
and m1.MyDate < m2.MyDate
and not exists(select * from MyTable m2a
where m2a.c_patient = m2.c_patient
and m2a.MyDate > m1.MyDate
and m2a.MyDate < m2.MyDate)
left join MyTable m3 on m2.c_patient = m3.c_patient
and m2.MyDate < m3.MyDate
and not exists(select * from MyTable m3a
where m3a.c_patient = m3.c_patient
and m3a.MyDate > m2.MyDate
and m3a.MyDate < m3.MyDate)
left join MyTable m4 on m3.c_patient = m4.c_patient
and m3.MyDate < m4.MyDate
and not exists(select * from MyTable m4a
where m4a.c_patient = m4.c_patient
and m4a.MyDate > m3.MyDate
and m4a.MyDate < m4.MyDate)
left join MyTable m5 on m4.c_patient = m5.c_patient
and m4.MyDate < m5.MyDate
and not exists(select * from MyTable m5a
where m5a.c_patient = m5.c_patient
and m5a.MyDate > m4.MyDate
and m5a.MyDate < m5.MyDate)
where not exists(select * from MyTable m1a
where m1a.c_patient = m1.c_patient
and m1a.MyDate < m1.MyDate)

HTH,
Set

Alejandro Garcia wrote:

> 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 i=
> s the date that a test is applied to that patient ITEM is the item (a quest=
> ion) of the test and POINTS are the points the patient took that date for t=
> hat item.
> =20=20=20
> 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=3DITEM12=3DITEM13=3DITEM14=3DITEM15 =3D'Item' it's the only item =
> that we have to use for creating NEWTABLE
> =20=20=20
> 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 'It=
> em'.
> 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.

[Non-text portions of this message have been removed]