🚀 go-pugleaf

Hi! Thanks for this, it's almost working, as you said I'm getting too many inserts: one for every row in MyTable, so in newTable I have each row repeated 3 times (I'm using 3 items) so for each row I have that row repeated 3 times:

PatientID   Date     Item1  Item2   Item3
5001    19/09/2006     4      3      6
5001    19/09/2006     4      3      6
5001    19/09/2006     4      3      6

But I don't realize how to do just one insert (the only part you left for me to do!!!!!!) 






=?ISO-8859-1?Q?Svein_Erling_Tysv=E6r?= wrote:
>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