select "IdPatient", "Date",
sum(case when "IdTask" = 'Task1' then "Points" else null end) as "Task1",
sum(case when "IdTask" = 'Task2' then "Points" else null end) as "Task2",
sum(case when "IdTask" = 'Task3' then "Points" else null end) as "Task3",
sum(case when "IdTask" = 'Task4' then "Points" else null end) as "Task4"
from patients_executing_tasks
group by "IdPatient", "Date";

You can replace 'sum' with 'avg' or whatever rule makes most sense to combine multiple tasks per
day into just one value. These aggregate functions return null for empty (or all-null) datasets,
which is probably what you want.

-Philip

---------- Original Message -----------
From: Alejandro Garcia <aleplgr@...>
To: firebird-support@yahoogroups.com
Sent: Mon, 25 Aug 2008 08:11:42 +0000 (GMT)
Subject: [firebird-support] SQL for traspose

> Hi! I'm stuck with this query I hope someone can help me: I have this table for Patients

executing tasks:

> IdPatient  Date IdTask Points
>  
> where IdTask is the name of each task, there are 4 different tasks: Task1, Task2, Task3, Task4,

Date is the date the idPatient executed the idTask and Points is a number
showing the punctuation he took in the task 

>  
> My result table should look like this:
>  
> IdPatient Date Task1 Task2 Task3 Task4
>  
> For each idTask showing the Points each idPatient took each Date.
>  
> Each idPatient can execute the same idTask more than once in one Date or in different Dates.  Not

all idPatients execute every task so there will be some null values.

>  
> Thanks