SQL: How to club multiple rows into one having same id

Question

I have the following query

select r.role_id, 
r.role_name, 
p.permission_name 
from t_user_role ur, t_role r, t_role_permission rp, t_permission p 
where ur.role_id=r.role_id 
and ur.role_id=rp.role_id 
and rp.permission_id=p.permission_id
and ur.contact_id=505;

which gives me the follwing result

I want to club the rows having similar row id to get the result like below:

ROLE_ID                   ROLE_NAME               PERMISSION_NAME   
100                       ops_center               List Dashboard
106                       oc_public                RunETN Activity Detail | Run Test View | .... and so on

Please Help

Answer 1

Based on your statement below, I am assuming you are using MySQL and have tried using GROUP_CONCAT():

I have tried using Group Contact but I am not able to make a query.

If you are using MySQL then you will apply GROUP_CONCAT() like this:

select r.role_id, 
  r.role_name, 
  group_concat(p.permission_name separator ' | ') permission_name
from t_user_role ur
inner join t_role r
  on ur.role_id=r.role_id 
inner join t_role_permission rp
  on ur.role_id=rp.role_id 
inner join t_permission p 
  on rp.permission_id=p.permission_id
where ur.contact_id=505
group by r.role_id, r.role_name;

Note: I changed the query to use ANSI JOIN syntax instead of a comma separated list of tables

In Oracle 11g, you can use LISTAGG():

select r.role_id, 
  r.role_name, 
  LISTAGG(p.permission_name, ' | ') WITHIN GROUP (ORDER BY r.role_id) permission_name
from t_user_role ur
inner join t_role r
  on ur.role_id=r.role_id 
inner join t_role_permission rp
  on ur.role_id=rp.role_id 
inner join t_permission p 
  on rp.permission_id=p.permission_id
where ur.contact_id=505
group by r.role_id, r.role_name;