🚀 go-pugleaf

The below SQL is hopefully enough for you to get the hang of how it can 
be done. Though there are small catches due to me not quite 
understanding your problem.

MyTable M I just use to find the PatientID, you cannot do it exactly as 
I've done here, because you would get far too many inserts (at least one 
for every row in MyTable).

I have not done anything with the date except duplicating your code - 
you want CURRENT_DATE or TODAY or some other date, that's up to you.

I have assumed that all items are optional, i.e. Item1 may or may not 
exist for any particular patient. If they are not optional and all 
patients have done all four tests, just delete 'left'.

Lastly, I've only written one SQL statement to insert and not done any 
delete.

HTH,
Set

insert into newTable(firstpatient, Date, t1, t2, t3, t4)
select m.patientid, Date, m1.Points, m2.Points, m3.Points, m4.Points
from MyTable M
left join MyTable M1
   on M1.PatientID = M.PatientID
  and M1.Items = 'Item1'
left join MyTable M2
   on M2.PatientID = M.PatientID
  and M2.Items = 'Item2'
left join MyTable M3
   on M3.PatientID = M.PatientID
  and M3.Items = 'Item3'
left join MyTable M4
   on M4.PatientID = M.PatientID
  and M4.Items = 'Item4'

aleplgr wrote:
> 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 doesn't look good