sqloraclebi-publisher

Suppress Repeating values in Oracle sql


I'm using Oracle BIPublisher to build a sql query. Right now my query output is:

ControlID  Role
1          ABC
1          SJD
1          DKF
2          LLL
2          IJK

How do I write the sql so that the output comes out like this?

ControlID  Role   
1          ABC
           SJD
           DKF
2          LLL
           IJK

*** Edit: I was able to suppress using Tim's solution:

SELECT DISTINCT
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY GRCC_CONTROL_ID  ORDER BY erp_user_name) = 1
         THEN GRCC_CONTROL_ID  ELSE NULL END AS GRCC_CONTROL_ID ,
    ,role
from GRC_CTRL_AAC_INCIDENTS 
ORDER BY GRCC_CONTROL_ID, role

I want to replace the control_id with the actual control name but that is in a separate table. When I replace c.name in the above query, I get an error that says

'FROM' keyword not found where expected

:

SELECT DISTINCT
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY c.name  ORDER BY role) = 1
         THEN c.name  ELSE NULL END AS c.name ,
    role
from GRC_CTRL_AAC_INCIDENTS 
,GRC_CTRL_CCM_CONTROL_TL c
where GRCC_CONTROL_ID = c.id
ORDER BY c.name, role

Solution

  • This is really a presentation requirement, and so might be best handled in your presentation layer (e.g. something like PHP or Java). That being said, we could handle this using ROW_NUMBER:

    SELECT
        CASE WHEN ROW_NUMBER() OVER (PARTITION BY ControlID ORDER BY Role) = 1
             THEN ControlID ELSE NULL END AS ControlID,
        t.Role
    FROM yourTable t
    ORDER BY
        t.ControlID,
        t.Role;
    

    enter image description here

    Demo

    Edit:

    You are using the old style pre ANSI-92 join syntax is your actual updated query attempt. Don't do that. Instead, use an explicit join, with aliases:

    SELECT DISTINCT
        CASE WHEN ROW_NUMBER() OVER (PARTITION BY c.name ORDER BY role) = 1
             THEN c.name ELSE NULL END AS name,  -- NOT c.name
        role
    FROM GRC_CTRL_AAC_INCIDENTS t
    INNER JOIN GRC_CTRL_CCM_CONTROL_TL c
        ON t.GRCC_CONTROL_ID = c.id
    ORDER BY
        c.name,
        role;