Sample Scripts to Convert the Rows into One Column with seperator

 SELECT header_id, RTRIM(EXTRACT(EXML,'//ordereditem/text()'),',') ORDERED_ITEM
   FROM (SELECT E.header_id,
          xmlelement("HDR",
              XMLAGG(xmlelement("ordereditem",E.ordered_item||',') ORDER BY ordered_item))EXML
   FROM oe_order_lines_all E
   where header_id= 278563
   GROUP BY E.header_id);
***************************************************************************
SELECT header_id, LTRIM( SYS_CONNECT_BY_PATH( ORDERED_ITEM, ',' ), ',' ) iTEM_STRING
      FROM (SELECT LINE_ID, HEADER_ID, ORDERED_ITEM, ROW_NUMBER( ) OVER (PARTITION BY HEADER_ID ORDER BY ROWNUM) RN
              FROM OE_ORDER_LINES_aLL
             WHERE header_id = 278563)
     WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY HEADER_ID = PRIOR HEADER_ID
       AND RN = PRIOR RN + 1
START WITH rn = 1
  ORDER BY HEADER_ID;
***************************************************************************
SELECT   header_id
       , RTRIM(XMLAGG(XMLELEMENT(e
                               , ordered_item || ','
                                )).EXTRACT('//text()')
             , ','
              ) ordered_item
    FROM oe_order_lines_All
    where header_id = 278563
GROUP BY header_Id
***************************************************************************
SELECT     header_id
         , SUBSTR(SYS_CONNECT_BY_PATH(ordered_item
                                    , ','
                                     )
                , 2
                 ) name_list
      FROM (SELECT ordered_item
                 , header_id
                 , COUNT(*) OVER(PARTITION BY header_id) cnt
                 , ROW_NUMBER() OVER(PARTITION BY header_id ORDER BY ordered_item) seq
              FROM oe_order_lines_all
             WHERE header_id = 278563)
     WHERE seq = cnt
START WITH seq = 1
CONNECT BY PRIOR seq + 1 = seq
       AND PRIOR header_id = header_id;