You Asked
EncounterTable.PatEnc_ID Number(18)
FeedingTable.Recorded_Time Date
FeedingTable.Value Varchar2(2500 Byte)
FeedingTable.Value_comment Varchar2(255 Byte)
FeedngTable.RowTypeID Varchar2(18Byte)
FeedingTable.Pat_ID Number(18)
Select
EncounterTable.PatEnc_ID
, FeedingTable.Recorded_Time
FROM
EncounterTable
Left Outer Join FeedingTable ON EncounterTable.PatEnc_ID = FeedingTable.Pat_ID
Where
FeedngTable.RowTypeID='707903'
ORDER BY
EncounterTable.PatEnc_ID
, FeedingTable.Recorded_Time
PatEnc_ID Recorded_Time
1234 8/9/2015 6:00:00 AM
1234 8/9/2015 8:30:00 AM
1234 8/9/2015 9:48:00 AM
1234 8/9/2015 12:00:00 PM
1234 8/9/2015 12:15:00 AM
1234 8/9/2015 3:30:00 AM
1234 8/9/2015 4:00:00 AM
1234 8/9/2015 4:52:00 AM
1234 8/9/2015 5:35:00 AM
1234 8/9/2015 10:00:00 AM
6789 8/9/2015 2:00:00 AM
6789 8/9/2015 5:00:00 AM
6789 8/9/2015 7:52:00 AM
6789 8/9/2015 11:50:00 AM
6789 8/9/2015 1:30:00 PM
6789 8/9/2015 2:36:00 PM
6789 8/9/2015 3:00:00 PM
6789 8/9/2015 5:50:00 PM
6789 8/9/2015 9:00:00 PM
6789 8/10/2015 12:00:00 AM
6789 8/10/2015 1:00:00 AM
6789 8/10/2015 2:00:00 AM
6789 8/10/2015 4:00:00 AM
6789 8/10/2015 5:58:00 AM
6789 8/10/2015 7:00:00 AM
6789 8/10/2015 8:55:00 AM
6789 8/10/2015 10:00:00 AM
6789 8/10/2015 12:00:00 PM
Desired Output using the most optimized query:
PatEnc_ID Feedings
1234 8/9/2015 6:00:00 AM, 8/9/2015 8:30:00 AM, 8/9/2015 9:48:00 AM, 8/9/2015 12:00:00 PM, 8/9/2015 12:15:00 AM, 8/9/2015 3:30:00 AM, 8/9/2015 4:00:00 AM, 8/9/2015 4:52:00 AM, 8/9/2015 5:35:00 AM, 8/9/2015 10:00:00 AM
6789 8/9/2015 2:00:00 AM, 8/9/2015 5:00:00 AM, 8/9/2015 7:52:00 AM, 8/9/2015 11:50:00 AM, 8/9/2015 1:30:00 PM, 8/9/2015 2:36:00 PM, 8/9/2015 3:00:00 PM, 8/9/2015 5:50:00 PM, 8/9/2015 9:00:00 PM, 8/10/2015 12:00:00 AM, 8/10/2015 1:00:00 AM, 8/10/2015 2:00:00 AM, 8/10/2015 4:00:00 AM, 8/10/2015 5:58:00 AM, 8/10/2015 7:00:00 AM, 8/10/2015 8:55:00 AM, 8/10/2015 10:00:00 AM, 8/10/2015 12:00:00 PM
As I enter this question in the posting box, I am unable to format the fields so that the Feedings appear in one row. But that is what I am trying to achieve.
I have tried the following query but because the of the aggregate, I only get one value repeated numerous times in the Feedings column
SELECT PatEnc_ID,
LTRIM(MAX(SYS_CONNECT_BY_PATH(RECORDED_TIME,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS feeding
FROM (SELECT EncounterTable.PatEnc_ID,
FeedingTable.RECORDED_TIME,
ROW_NUMBER() OVER (PARTITION BY EncounterTable.PatEnc_ID ORDER BY FeedingTable.RECORDED_TIME) AS curr,
ROW_NUMBER() OVER (PARTITION BY EncounterTable.PatEnc_ID ORDER BY FeedingTable.RECORDED_TIME) -1 AS prev
FROM EncounterTable
LEFT OUTER JOIN FeedingTable ON EncounterTable.PatEnc_ID =FeedingTable.Pat_ID
WHERE
FeedingTable.FLO_MEAS_ID ='707903'
)
GROUP BY PatEnc_ID
CONNECT BY prev = PRIOR curr AND PatEnc_ID = PRIOR PatEnc_ID
START WITH curr = 1;
and Chris said...
Thanks for a clear description of your request Michal. Please include create table statements and inserts in future, it makes it much easier for us to help you!
You can use listagg() to convert rows into a comma separated string.
Here's a simplified example based on your data:
alter session set nls_date_format = 'dd/mm/yyyy hh:mi:ss am';
create table FeedingTable (
Pat_ID Number(18),
Recorded_Time Date
);
insert into FeedingTable
values (1234, '8/9/2015 4:52:00 AM');
insert into FeedingTable
values (1234 , '8/9/2015 5:35:00 AM');
insert into FeedingTable
values (1234 , '8/9/2015 10:00:00 AM');
insert into FeedingTable
values (6789 , '8/9/2015 2:00:00 AM');
insert into FeedingTable
values (6789 , '8/9/2015 5:00:00 AM');
insert into FeedingTable
values (6789 , '8/9/2015 7:52:00 AM');
commit;
select pat_id,
listagg(Recorded_Time, ',') within group (order by recorded_time) dts
from feedingtable
group by pat_id;
PAT_ID DTS
---------- ------------------------------------------------------------
1234 08/09/2015 04:52:00,08/09/2015 05:35:00,08/09/2015 10:00:00
6789 08/09/2015 02:00:00,08/09/2015 05:00:00,08/09/2015 07:52:00