Identifying start and end of period covered - orac

Question

I have data that looks like this:

        ID      FROM        TO          START_CODE      END_CODE    TYPE
        A       01/01/2012  02/02/2012  P               E           1
        A       12/03/2011  01/01/2012  P               X           1
        A       01/01/2011  12/03/2011  S               X           2

        A       01/01/2010  02/02/2010  P               E           2
        A       12/03/2010  01/01/2010  P               X           4
        A       01/01/2009  12/03/2009  S               X           1

Basically, this is information for one client. The end_code 'e' denotes the end of a period of care, the start_code 's' denotes a start of a period of care. A start code of 'p' denotes the dates are a continuation, as does an end code of 'x'. What I would like to be able to return is data that looks like the following:

        ID      START       END             Types
        A       01/01/2011  02/02/2012      2,1,1
        A       01/01/2009  02/02/2010      1,4,2

The query for the first table looks similar to this (example only), however, I would be interested in returning for a number of clients, and not just 'A'.

        SELECT
        A.ID,
        A.FROM,
        A.TO,
        A.START_CODE,
        A.END_CODE,
                       A.Type

        WHERE
        A.ID = 'A'

I've used xmlagg in the past to achieve similar things (i.e. to list information in one cell), but it's actually identifying seperate start and end dates for each client, and then returning the sequence of types that I'm struggling with. For reference, my oracle version is 10g, 10.2.0.5.0.

Thanks for your time, any pointers or assistance is appreciated.

Edited to include suggestion by A.B.Cade below:

        SELECT
        t3.MOV_PER_GRO_ID,
        t3.f,
        t3.MOV_END_DATE,
        t3.types,
        LENGTH(REGEXP_REPLACE(t3.types,'[^,]')) as "Count"
        FROM(

        SELECT 
        sys_connect_by_path(t2.MOV_2000_PLACEMENT_TYPE,',') types,
        connect_by_root(t2.MOV_START_DATE) f, 
        t2.MOV_START_DATE,        
        t2.MOV_END_DATE,        
        connect_by_isleaf is_leaf, 
        t2.MOV_PER_GRO_ID 
        FROM (SELECT t.*,
        lag(t.MOV_START_DATE) over (ORDER BY t.MOV_PER_GRO_ID, t.MOV_START_DATE) nfrom
        FROM O_MOVEMENTS t
        WHERE t.MOV_PER_GRO_ID IN ('A','B'))t2 
        START 
        WITH 
        t2.MOV_2000_START_REASON = 'S'
        CONNECT BY   
        PRIOR t2.MOV_START_DATE = t2.nfrom
        AND PRIOR t2.MOV_PER_GRO_ID =  t2.MOV_PER_GRO_ID 
        AND t2.MOV_2000_START_REASON IN ('P'))t3
        where t3.is_leaf=1

Updated based on ABCade's solution. After a few tweaks (again thanks to ABCade) it appears to be working.

Answer 1

Try:

SELECT  t3."ID", t3.f "start", t3."TO" "end", t3.types
FROM (
SELECT sys_connect_by_path(t2."TYPE",',') types,
       connect_by_root(t2."FROM") f,
       t2."FROM",
       t2."TO",
       connect_by_isleaf is_leaf ,
t2."ID"
FROM (
  SELECT t.*, lag(t."FROM") over (ORDER BY t."FROM") nfrom
    FROM table1 t
   WHERE t."ID" = 'A'
  ) t2
START WITH t2."START_CODE" = 'S'
CONNECT BY   PRIOR t2."FROM" = t2.nfrom  AND t2."START_CODE" = 'P') t3
WHERE is_leaf=1

Here is a fiddle
Here is another fiddle (after seeing your comment and update)