CODEWITHSUNDEEP
sqloracle-database
vivek
vivek

Reputation: 111

how to create multiple conditions on same column in SQL

i am trying to get some data is a specific format.Currently i have the below data in a table:

TARGET_NAME      KEY_VALUE                     METRIC_COLUMN  VALUE
-----------------------------------------------------------------------------
orclDB1_host    orclDB1_host:DBMS_CREDENTIAL    TABLE_NAME  DBMS_CREDENTIAL
orclDB1_host    orclDB1_host:DBMS_DDL           TABLE_NAME  DBMS_DDL
orclDB1_host    orclDB1_host:DBMS_LOB           TABLE_NAME  DBMS_LOB
orclDB1_host    orclDB1_host:DBMS_RANDOM        TABLE_NAME  DBMS_RANDOM
orclDB1_host    orclDB1_host:DBMS_SQL           TABLE_NAME  DBMS_SQL
orclDB1_host    orclDB1_host:DBMS_CREDENTIAL    GRANTEE     PUBLIC
orclDB1_host    orclDB1_host:DBMS_DDL           GRANTEE     PUBLIC
orclDB1_host    orclDB1_host:DBMS_LOB           GRANTEE     PUBLIC
orclDB1_host    orclDB1_host:DBMS_RANDOM        GRANTEE     PUBLIC
orclDB1_host    orclDB1_host:DBMS_SQL           GRANTEE     PUBLIC
orclDB1_host    orclDB1_host:DBMS_CREDENTIAL    PRIVILEGE   EXECUTE
orclDB1_host    orclDB1_host:DBMS_DDL           PRIVILEGE   EXECUTE
orclDB1_host    orclDB1_host:DBMS_LOB           PRIVILEGE   EXECUTE
orclDB1_host    orclDB1_host:DBMS_RANDOM        PRIVILEGE   EXECUTE
orclDB1_host    orclDB1_host:DBMS_SQL           PRIVILEGE   EXECUTE

i need the above data in this format:

DB_TABLE                        TABLE_NAME         GRANTEE   PRIVILEGE
------------------------------------------ ----------------------------
orclDB1_host:DBMS_CREDENTIAL     DBMS_CREDENTIAL   PUBLIC   EXECUTE
orclDB1_host:DBMS_RANDOM         DBMS_RANDOM       PUBLIC   EXECUTE
orclDB1_host:DBMS_DDL            DBMS_DDL          PUBLIC   EXECUTE
orclDB1_host:DBMS_SQL            DBMS_SQL          PUBLIC   EXECUTE
orclDB1_host:DBMS_LOB            DBMS_LOB          PUBLIC   EXECUTE

i tried the below sql,but am only able to get this:

select distinct KEY_VALUE as DB_AND_TABLE,VALUE as GRANTEE from mgmt$metric_current 
where metric_name='ME$PUBLICPRIVS' 
and target_name like 'orclDB1_host%' 
and COLUMN_LABEL='GRANTEE'

Result:

DB_AND_TABLE                 GRANTEE
---------------------------------------
orclDB1_host:DBMS_LOB         PUBLIC
orclDB1_host:DBMS_RANDOM      PUBLIC
orclDB1_host:DBMS_SQL         PUBLIC
orclDB1_host:DBMS_DDL         PUBLIC
orclDB1_host:DBMS_CREDENTIAL  PUBLIC

Can you please help.Thanks.

Upvotes: 1

Views: 66

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Use conditional aggregation:

select KEY_VALUE as DB_AND_TABLE,
       max(case when METRIC_COLUMN = 'TABLE_NAME' then VALUE end) as table_name,
       max(case when METRIC_COLUMN = 'GRANTEE' then VALUE end) as GRANTEE,
       max(case when METRIC_COLUMN = 'PRIVILEGE' then VALUE end) as PRIVILEGE
from mgmt$metric_current 
group by key_value;

Upvotes: 1

MT0
MT0

Reputation: 168416

Use a PIVOT:

SELECT *
FROM   mgmt$metric_current
PIVOT (
  MAX( value ) FOR metric_column IN (
    'TABLE_NAME' AS table_name,
    'GRANTEE' AS grantee,
    'PRIVILEGE' AS privilege
  )
)

Which, for your test data:

CREATE TABLE mgmt$metric_current ( TARGET_NAME, KEY_VALUE, METRIC_COLUMN, VALUE ) AS
SELECT 'orclDB1_host', 'orclDB1_host:DBMS_CREDENTIAL', 'TABLE_NAME', 'DBMS_CREDENTIAL' FROM DUAL UNION ALL
SELECT 'orclDB1_host', 'orclDB1_host:DBMS_DDL',        'TABLE_NAME', 'DBMS_DDL' FROM DUAL UNION ALL
SELECT 'orclDB1_host', 'orclDB1_host:DBMS_LOB',        'TABLE_NAME', 'DBMS_LOB' FROM DUAL UNION ALL
SELECT 'orclDB1_host', 'orclDB1_host:DBMS_RANDOM',     'TABLE_NAME', 'DBMS_RANDOM' FROM DUAL UNION ALL
SELECT 'orclDB1_host', 'orclDB1_host:DBMS_SQL',        'TABLE_NAME', 'DBMS_SQL' FROM DUAL UNION ALL
SELECT 'orclDB1_host', 'orclDB1_host:DBMS_CREDENTIAL', 'GRANTEE',    'PUBLIC' FROM DUAL UNION ALL
SELECT 'orclDB1_host', 'orclDB1_host:DBMS_DDL',        'GRANTEE',    'PUBLIC' FROM DUAL UNION ALL
SELECT 'orclDB1_host', 'orclDB1_host:DBMS_LOB',        'GRANTEE',    'PUBLIC' FROM DUAL UNION ALL
SELECT 'orclDB1_host', 'orclDB1_host:DBMS_RANDOM',     'GRANTEE',    'PUBLIC' FROM DUAL UNION ALL
SELECT 'orclDB1_host', 'orclDB1_host:DBMS_SQL',        'GRANTEE',    'PUBLIC' FROM DUAL UNION ALL
SELECT 'orclDB1_host', 'orclDB1_host:DBMS_CREDENTIAL', 'PRIVILEGE',  'EXECUTE' FROM DUAL UNION ALL
SELECT 'orclDB1_host', 'orclDB1_host:DBMS_DDL',        'PRIVILEGE',  'EXECUTE' FROM DUAL UNION ALL
SELECT 'orclDB1_host', 'orclDB1_host:DBMS_LOB',        'PRIVILEGE',  'EXECUTE' FROM DUAL UNION ALL
SELECT 'orclDB1_host', 'orclDB1_host:DBMS_RANDOM',     'PRIVILEGE',  'EXECUTE' FROM DUAL UNION ALL
SELECT 'orclDB1_host', 'orclDB1_host:DBMS_SQL',        'PRIVILEGE',  'EXECUTE' FROM DUAL;

Outputs:

TARGET_NAME  | KEY_VALUE                    | TABLE_NAME      | GRANTEE | PRIVILEGE
:----------- | :--------------------------- | :-------------- | :------ | :--------
orclDB1_host | orclDB1_host:DBMS_SQL        | DBMS_SQL        | PUBLIC  | EXECUTE  
orclDB1_host | orclDB1_host:DBMS_DDL        | DBMS_DDL        | PUBLIC  | EXECUTE  
orclDB1_host | orclDB1_host:DBMS_RANDOM     | DBMS_RANDOM     | PUBLIC  | EXECUTE  
orclDB1_host | orclDB1_host:DBMS_CREDENTIAL | DBMS_CREDENTIAL | PUBLIC  | EXECUTE  
orclDB1_host | orclDB1_host:DBMS_LOB        | DBMS_LOB        | PUBLIC  | EXECUTE

db<>fiddle here

Upvotes: 2

Related Questions