Oracle: Conditional Grouping

Question

I am having the data in following format :

 cons_Type   COLUMN_NAME
    P        (COL1)
    R        (COL6_REFERENCE)
    R        (COL6_REFERENCE)
    U        (COL5_COM_UNIQUE)
    U        (COL3_UNIQUE,COL4_COM_UNIQUE)

Finally, I want to listAgg column_name, cons_type wise where cons_type will be either 'P' or 'U' only.

Other cons_type like 'R' must not be list aggregated using LISTAGG() function.

and final expected output must be in following format.

cons_Type   COLUMN_NAME
P            (COL1)
R            (COL6_REFERENCE)
R            (COL6_REFERENCE)
U            (COL3_UNIQUE,COL4_COM_UNIQUE),(COL5_COM_UNIQUE)

Answer 1

Try:

select 
    "cons_Type", 
    "COLUMN_NAME"
from tbl
where "cons_Type" not in ('P', 'U')

union all

select 
    "cons_Type", 
    LISTAGG("COLUMN_NAME" , ',') WITHIN GROUP (ORDER BY "cons_Type")
from tbl
where "cons_Type" in ('P', 'U')
group by "cons_Type"

order by "cons_Type"

Demo sqlfiddle