Pivoting - Multi column. in One

Ref. http://www.dba-oracle.com/t_sql_pivoting_rows_several_columns.htm

Using the SYS_CONNECT_BY_PATH operator

select

deptno,
substr(sys_connect_by_path(lname, ','),2) name_list from
( select lname,
           deptno,
           count(*) Over (partition by deptno ) cnt,
           Row_Number() Over(partition by deptno order by lname) seq
  from emp
   where deptno is not null
)
where seq=cnt
start with seq=1
connect by prior seq+1=seq and prior deptno=deptno; 　

Select pole_code, genPact_le_code,coe_code, sdu_code,
        substr(sys_connect_by_path(user_id, ','), 2) name_list
From (select user_id, pole_code, genPact_le_code,coe_code, sdu_code,
                count(1) over(partition by role_id, pole_code, genPact_le_code,coe_code, sdu_code) Cnt,
                Row_Number()over(partition by role_id, pole_code, genPact_le_code,coe_code,sdu_code 
                 order by user_id) seq
           from gbs_userroleactivity_mst where role_id = 1
       --and pole_code='10' and genpact_le_code='D84' and sdu_code='BD'
) where seq= cnt
start with seq=1
connect by prior seq+1=seq and prior sdu_code = sdu_code and prior pole_code=pole_code and prior coe_code=coe_code and prior genpact_le_code=genpact_le_code;


Using the Listagg  function [Oracle 11]

Ref. http://docs.oracle.com/cd/E14072_01/server.112/e10592/functions087.htm

• As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row.
• As a group-set aggregate, the function operates on and returns an output row for each group defined by the GROUP BY clause.
• As an analytic function, LISTAGG partitions the query result set into groups based on one or more expression in the query_partition_clause.

select pole_code, genPact_le_code,coe_code, sdu_code ,
(select listagg(user_id, ',') within group (order by created_date desc ) from gbs_user_mst
where user_id in (select distinct user_id from gbs_userroleactivity_mst where role_id = 1 and pole_code =a.pole_code and genpact_le_code = a.genpact_le_code
and coe_code = a.coe_code and sdu_code = a.sdu_code)) billing_master_maker
From gbs_billingbase_mst