Subject Re: Create columns from rows
Author mspencewasunavailable
--- In, Alejandro Garcia
<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 does not look good..
See if this does what you want:

insert into newtable( patientid, testdate, t1, t2,t3,t4)
patientID, TestDate,
case when item = 1 then sum(points) else 0 end as I1,
case when item = 2 then sum(points) else 0 end as I2,
case when item = 3 then sum(points) else 0 end as I3,
case when item = 4 then sum(points) else 0 end as I4
from mytable
group by patientID, TestDate, item

Michael D. Spence
Mockingbird Data Systems, Inc.