--- In firebird-support@yahoogroups.com, 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)
select
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.