Hi! I'm trying to create columns of a new table from row values.
Now I have one table which describes the applications of a test to patients, the test consists of 4 questions (items) the table looks like this:
PatientID Date Items Points
Date is the date the test took place, Items are the 4 questions (Item1, Item2, Item3 and Item4) and Points are the points that PatientID took in each item that date.

And I need to create a new table that looks like this:
PatientID Date Item1 Item2 Item3 Item4
a column for each item, so in a row I'll have for each PatientID the date the test took place and the points he got for each of the four items.

I'm trying to do something like this:

totpatients = select COUNT (PatientID) from mytable
counter =0
WHILE counter < totpatients DO
firstpatient = select MIN (PatientID) from mytable

select Points from myTable (where Items=Item1 AND PatientID = firstpatient) into :t1
select Points from myTable where Items=Item2 AND PatientID = firstpatient) into :t2
.
.
insert into newTable( firstpatient, Date, t1, t2,t3,t4)
values (PatientID, Date, :t1, :t2,:t3,:t4)

Delete firstpatient from myTable
counter ++

END While


But this does not look good..



---------------------------------

LLama Gratis a cualquier PC del Mundo.
Llamadas a fijos y móviles desde 1 céntimo por minuto.
http://es.voice.yahoo.com

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