PLSQL 10g- create a list of strings

Question

I have been researching this for a while. I have a Dataset below, and I need the desired out out to be in a list of strings. If there is a duplicate Product I want it to go on a seperate row. what the below gets me is all of the customerid and products on one line. I want if there are duplicates make the duplicate on anothe line. Please let me know if there are any questions.

With CustomerData as (
select
ROW_NUMBER() OVER (PARTITION BY customerid ORDER BY Product) rn,
COUNT(*) OVER (PARTITION BY customerid ) cnt
from Customers)
select 
ltrim(sys_connect_by_path(customerid,','),','),
ltrim(sys_connect_by_path(Product,','),',') AS Product,
from CustomerData 
where rn = cnt 
start with rn = 1 
connect by prior customerid = customerid
and prior rn = rn1 


customerid | CustomerName | Product | date 
1            Bob            9         3-14-2016
1            Bob            10        3-14-2016       
1            Bob            9         3-12-2016
2            Brad           1         3-14-2016
2            Brad           3         3-14-2016
3            Sam            1         3-14-2016
3            Sam            1         3-12-2016
3            Sam            5         3-14-2016

Desired Output

customerid   CustomerName      Product   
1, 1         BOB, BOB         9, 10
1,           BOB              9
2, 2         Brad, Brad       1, 3
3, 3         Sam, Sam         1, 5
3            Sam              1

Answer 1

On Oracle 10 you should be able to use the wmsys.wm_concat function, although do be aware that the return datatype is a CLOB and not a varchar:

WITH the_Data as (
select 1 cust_id,           'Bob' cust_name,           9 prod_id,        to_Date('3-14-2016','mm-dd-yyyy') order_dt from dual union all
select 1,            'Bob',            10,        to_Date('3-14-2016','mm-dd-yyyy') order_dt from dual union all       
select 1,            'Bob',            9,        to_Date('3-12-2016','mm-dd-yyyy') order_dt from dual union all
select 2,            'Brad',           1,        to_Date('3-14-2016','mm-dd-yyyy') order_dt from dual union all
select 2,            'Brad',           3,        to_Date('3-14-2016','mm-dd-yyyy') order_dt from dual union all
select 3,            'Sam',            1,        to_Date('3-14-2016','mm-dd-yyyy') order_dt from dual union all
select 3,            'Sam',            1,        to_Date(' 3-12-2016','mm-dd-yyyy') order_dt from dual union all
select 3,            'Sam',            5,        to_Date('3-14-2016','mm-dd-yyyy') order_dt from dual 
)
select wm_concat(cust_id)
      ,wm_concat(cust_name)
      ,wm_concat(prod_id) 
 from the_Data
group by order_dt, cust_id
order by cust_id, order_Dt  

WM_CONCAT(CUST_ID),WM_CONCAT(CUST_NAME),WM_CONCAT(PROD_ID)
1                 Bob                  9
1,1               Bob,Bob              9,10
2,2               Brad,Brad            1,3
3                 Sam                  1
3,3               Sam,Sam              1,5

If you don't have access to this (it is not a supported function nor always installed), then you can implement Tom Kyte's STRAGG user-defined analytic as shown here

Other than that, you wind up going into XML processing as an option:

WITH the_Data as (
select 1 cust_id,           'Bob' cust_name,           9 prod_id,        to_Date('3-14-2016','mm-dd-yyyy') order_dt from dual union all
select 1,            'Bob',            10,        to_Date('3-14-2016','mm-dd-yyyy') order_dt from dual union all       
select 1,            'Bob',            9,        to_Date('3-12-2016','mm-dd-yyyy') order_dt from dual union all
select 2,            'Brad',           1,        to_Date('3-14-2016','mm-dd-yyyy') order_dt from dual union all
select 2,            'Brad',           3,        to_Date('3-14-2016','mm-dd-yyyy') order_dt from dual union all
select 3,            'Sam',            1,        to_Date('3-14-2016','mm-dd-yyyy') order_dt from dual union all
select 3,            'Sam',            1,        to_Date(' 3-12-2016','mm-dd-yyyy') order_dt from dual union all
select 3,            'Sam',            5,        to_Date('3-14-2016','mm-dd-yyyy') order_dt from dual 
)
select rtrim(xmlagg(xmlelement(e,cust_id,',').extract('//text()')),',') cus_ids
      ,rtrim(xmlagg(xmlelement(e,cust_name,',').extract('//text()')),',') cus_names
      ,rtrim(xmlagg(xmlelement(e,prod_id,',').extract('//text()')),',') prod_ids
 from the_Data
group by order_dt, cust_id
order by cust_id, order_Dt ;

cus_ids,      cus_names,    prod_ids    
1             Bob           9
1,1           Bob,Bob       9,10
2,2           Brad,Brad     1,3
3             Sam           1
3,3           Sam,Sam       1,5

Answer 2

Without using the undocumented (and unsupported) WMSYS.WM_CONCAT function (that is not available on all systems and is not available at all on Oracle 12c) you can do it using collections and the COLLECT aggregation function.

Oracle Setup:

CREATE OR REPLACE TYPE VARCHAR2s_Table AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE FUNCTION concatStrings(
  Strs VARCHAR2s_Table,
  delim VARCHAR2 DEFAULT ','
) RETURN CLOB
AS
  out_string CLOB;
BEGIN
  FOR i IN 1 .. Strs.COUNT LOOP
    out_string := out_string || CASE WHEN i = 1 THEN '' ELSE delim END || Strs(i);
  END LOOP;
  RETURN out_string;
END;
/

Query

SELECT concatStrings( CAST( COLLECT( TO_CHAR( cust_id ) ) AS VARCHAR2s_Table ) ) AS cust_ids,
       concatStrings( CAST( COLLECT( cust_name ) AS VARCHAR2s_Table ) ) AS cust_names,
       concatStrings( CAST( COLLECT( TO_CHAR( prod_id ) ) AS VARCHAR2s_Table ) ) AS prod_ids
FROM   table_name
GROUP BY cust_id, order_dt;

Output:

CUST_IDS              CUST_NAMES            PROD_IDS
--------------------- --------------------- ---------------------
1                     Bob                   9
1,1                   Bob,Bob               9,10
2,2                   Brad,Brad             1,3  
3                     Sam                   1
3,3                   Sam,Sam               1,5