Select distinct C_PATIENT
, (Select first 1 Date
from C_PATIENT b
where b.C_PATIENT = a.C_PATIENT
order by b.C_PATIENT, b.DATE) as DATE1
, (Select first 1 ITEM
from C_PATIENT b
where b.C_PATIENT = a.C_PATIENT
order by b.C_PATIENT, b.DATE) as ITEM1
, (Select first 1 skip 1 Date
from C_PATIENT b
where b.C_PATIENT = a.C_PATIENT
order by b.C_PATIENT, b.DATE) as DATE2
, (Select first 1 skip 1 ITEM
from C_PATIENT b
where b.C_PATIENT = a.C_PATIENT
order by b.C_PATIENT, b.DATE) as ITEM2
, (Select first 1 skip 2 Date
from C_PATIENT b
where b.C_PATIENT = a.C_PATIENT
order by b.C_PATIENT, b.DATE) as DATE3
, (Select first 1 skip 2 ITEM
from C_PATIENT b
where b.C_PATIENT = a.C_PATIENT
order by b.C_PATIENT, b.DATE) as ITEM3
, (Select first 1 skip 3 Date
from C_PATIENT b
where b.C_PATIENT = a.C_PATIENT
order by b.C_PATIENT, b.DATE) as DATE4
, (Select first 1 skip 3 ITEM
from C_PATIENT b
where b.C_PATIENT = a.C_PATIENT
order by b.C_PATIENT, b.DATE) as ITEM4
, (Select first 1 skip 4 Date
from C_PATIENT b
where b.C_PATIENT = a.C_PATIENT
order by b.C_PATIENT, b.DATE) as DATE5
, (Select first 1 skip 4 ITEM
from C_PATIENT b
where b.C_PATIENT = a.C_PATIENT
order by b.C_PATIENT, b.DATE) as ITEM5
from C_PATIENT a

Regards Cao

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]On Behalf Of Alejandro Garcia
Sent: 18 May 2007 01:12
To: firebird-support@yahoogroups.com
Subject: [firebird-support] create columns from multiple rows


Hi! I have MYTABLE with this columns:
C_PATIENT, DATE, ITEM,POINTS
where C_PATIENT is the patient's code that identifies the patient, DATE is
the date that a test is applied to that patient ITEM is the item (a
question) of the test and POINTS are the points the patient took that date
for that item.

Each test is applied more than once to a patient in different dates, and I
need to create a NEWTABLE that looks like this:
C_PATIENT, DATE1,ITEM11,DATE2,ITEM12,DATE3,ITEM13,
DATE4,ITEM14,DATE5,ITEM15
ITEM11=ITEM12=ITEM13=ITEM14=ITEM15 ='Item' it's the only item that we have
to use for creating NEWTABLE

NEWTABLE has 11 columns, result of each application of the test and each
row contains the patient code, the test dates and the POINTS he took in
'Item'.
The test is applied at most 5 times to each patient, some patients took
the test once, others twice, other 3 times, other 4 times and others 5
times.
The question 'Item' in MYTABLE never has a null value.

Thanks for your time.


---------------------------------

¡Descubre una nueva forma de obtener respuestas a tus preguntas!
Entra en Yahoo! Respuestas.

[Non-text portions of this message have been removed]






[Non-text portions of this message have been removed]