How to concatenate multiple rows into one in oracle using wm_concat function

select d.username,d.account_status,d.Lock_date,d.expiry_date,d.created,r.granted_role

from dba_users d join DBA_ROLE_PRIVS r  on D.USERNAME = R.GRANTEE

where D.USERNAME= UPPER ('mahi')

AND d.username NOT IN (select distinct owner schema_name from dba_segments);

 

USERNAME

ACCOUNT_STATUS

LOCK_DATE

EXPIRY_DATE

CREATED

GRANTED_ROLE

MAHI

OPEN

 

 

2012-07-20 15:52

REP_RO

MAHI

OPEN

 

 

2012-07-20 15:52

SETTLEMENT_ADMIN

 

Now I want to show GRANTED_ROLE in single rows, I have used here function wm_concat for that.

 

select d.username,d.account_status,d.Lock_date,d.expiry_date,d.created,to_char(wm_concat(r.granted_role)) as role

from dba_users d join DBA_ROLE_PRIVS r  on D.USERNAME = R.GRANTEE

where D.USERNAME= UPPER ('mahi')

AND d.username NOT IN (select distinct owner schema_name from dba_segments)

group by d.username,d.account_status,d.Lock_date,d.expiry_date,d.created;

 

USERNAME

ACCOUNT_STATUS

LOCK_DATE

EXPIRY_DATE

CREATED

GRANTED_ROLE

MAHI

OPEN

 

 

2012-07-20 15:52

REP_RO,SETTLEMENT_ADMIN

 

Comments