Saturday 21 September 2013

Display single column wise record for multiple row wise records

select grantee,table_name,
NVL (MAX (CASE privilege when ‘INSERT’ then ‘I’ else null end ),’ ‘) as “Insert”,
NVL (MAX (CASE privilege when ‘UPDATE’ then ‘U’ else null end ),’ ‘) as “Update”,
NVL (MAX (CASE privilege when ‘DELETE’ then ‘D’ else null end ),’ ‘) as “Delete”,
NVL (MAX (CASE privilege when ‘SELECT’ then ‘S’ else null end ),’ ‘) as “Select”,
NVL (MAX (CASE privilege when ‘EXECUTE’ then ‘E’ else null end ),’ ‘) as “Execute”,
NVL (MAX (CASE privilege when ‘ALTER’ then ‘A’ else null end ),’ ‘) as “Alter”
from DBA_TAB_PRIVS
where grantee  in (‘ALEYA’,'JAHANGIR’,'LUCKY’,'MAHAMUD’,'MOHI’,'MOJLISH’,'MOKTER’,'RAKIB’,'SAIFUL’,'SHARIFUL’,'TAPAN’,'JASIM’,'KAWSIK’,'ALEYA’,
‘ATMUTL’,'BACH’,'BACHINT’,'FIUINT’,'ISLBAS’,'ISLIMG’,'ISLITS’,'ISLSYS’,'MYBANK’,'ORBBBR’,'ORBITS’,'SMSGTWAY’,'STFOREX’,'STLBAS’,'STLIMG’,'STLSYS’,'WEBADMIN’,
‘BACHINT’,'ISLBAS’,'ISLIMG’,'ISLSYS’,'MYBANK’,'ORBBBR’,'SMSGTWAY’,'STLBAS’,'STLIMG’,'STLSYS’,'MAMUN_ERA’)
group by grantee,table_name
order by grantee

1 comment: