Hi,
Well, yes, if you also have a primary key, then it is possible. I'll do things part by part, so that it is easy to understand and assume the following table:

PATIENT
PK IdPatient Date1 IdTask Points

First, you say that not all tasks may be done each day. Assuming that means that there is no task that will be done each day, we have to first get to the patients of interest for each day:

SELECT P.IdPatient, P.Date1
FROM PATIENTS P
LEFT JOIN PATIENTS P1 ON P.IdPatient = P1.IdPatient and P.Date1 = P1.Date1 and P.PK < P1.PK
WHERE P1.PK IS NULL

P1.PK IS NULL is there to ascertain only one match for each patient.

Now, let's add the tasks:

SELECT P.IdPatient, P.Date1, T1.Points as Task1, T2.Points as Task2, T3.Points as Task3, T4.Points as Task4
FROM PATIENTS P
LEFT JOIN PATIENTS P1 ON P.IdPatient = P1.IdPatient and P.Date1 = P1.Date1 and P.PK < P1.PK
LEFT JOIN PATIENTS T1 ON P.IdPatient = T1.IdPatient and P.Date1 = T1.Date1 and T1.IdTask = 'Task1'
LEFT JOIN PATIENTS T2 ON P.IdPatient = T2.IdPatient and P.Date1 = T2.Date1 and T2.IdTask = 'Task2'
LEFT JOIN PATIENTS T3 ON P.IdPatient = T3.IdPatient and P.Date1 = T3.Date1 and T3.IdTask = 'Task3'
LEFT JOIN PATIENTS T4 ON P.IdPatient = T4.IdPatient and P.Date1 = T4.Date1 and T4.IdTask = 'Task4'
WHERE P1.PK IS NULL

This may appear to give you the result you want, but things are complicated by the fact that people can do the tasks several times within one day. My hunch is that if a patient takes all four tests twice within a day, then you want two rows returned, whereas the above query would find the two Task1, link them to the two Task2 and so on, so that you end up with 16 rows!

To get it down to two rows, I must admit that I'm uncertain whether is possible or not (using a stored procedure it should be possible, and probably using the WITH statement of Firebird 2.1 as well) in a simple SQL statement using the current table. If you had another field 'Num' containing which time the current test was done the day in question (i.e. using 1 and 2 for each of the tests if all tests were executed twice on the same patient within on day), then you could try something like:

SELECT P.IdPatient, P.Date1, T1.Points as Task1, T2.Points as Task2, T3.Points as Task3, T4.Points as Task4
FROM PATIENTS P
LEFT JOIN PATIENTS P1 ON P.IdPatient = P1.IdPatient and P.Date1 = P1.Date1 and P.PK < P1.PK
LEFT JOIN PATIENTS T1 ON P.IdPatient = T1.IdPatient and P.Date1 = T1.Date1 and T1.IdTask = 'Task1'
LEFT JOIN PATIENTS T2 ON P.IdPatient = T2.IdPatient and P.Date1 = T2.Date1 and T2.IdTask = 'Task2' and T2.Num = coalesce(T1.Num, T2.Num)
LEFT JOIN PATIENTS T3 ON P.IdPatient = T3.IdPatient and P.Date1 = T3.Date1 and T3.IdTask = 'Task3' and T3.Num = coalesce(T1.Num, T2.Num, T3.Num)
LEFT JOIN PATIENTS T4 ON P.IdPatient = T4.IdPatient and P.Date1 = T4.Date1 and T4.IdTask = 'Task4' and T4.Num = coalesce(T1.Num, T2.Num, T3.Num, T4.Num)
WHERE P1.PK IS NULL

That should ensure that only records with the same NUM were linked together.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Alejandro Garcia
Sent: 25. august 2008 10:12
To: firebird-support@yahoogroups.com
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