CODEWITHSUNDEEP
sqloracle-databasegroup-byoracle-sqldeveloper
rmh13
rmh13

Reputation: 13

How to group by with no aggregate?

I'm trying to edit my sql so that it will show the gl_name, bu_name, and gl_name2 columns based on the role_name column. I can't do a group by because I have no aggregate. What is the best way to go about this? These are the results now:

enter image description here

My desired result would be one line that combines everything together like this:

enter image description here

My sql now:

select
pu.username,
role.ROLE_NAME,
gl.name gl_name,
bu.bu_name bu_name,
led.NAME gl_name2
from 
FUN_USER_ROLE_DATA_ASGNMNTS role,
gl_access_sets gl,
per_users pu,
FUN_ALL_BUSINESS_UNITS_V bu,
GL_LEDGERS led
where gl.ACCESS_SET_ID (+)= role.ACCESS_SET_ID 
and role.org_id = bu.bu_id (+)
and role.LEDGER_ID = led.LEDGER_ID (+)
and pu.USER_GUID = role.USER_GUID (+)
and role.role_name IS NOT NULL
and (gl.name IS NOT NULL or bu_name IS NOT NULL or led.name IS NOT NULL)
order by pu.username, role.role_name

Upvotes: 0

Views: 69

Answers (1)

Aleksej
Aleksej

Reputation: 22969

If I understand well, you may simply need this:

select username, role_name, max(gl_name), max(bu_name), max(gl_name2)
from 
     ...
group by username, role_name
order by username, role_name

Upvotes: 2

Related Questions