"Flatten" query results

[elsenorjose]

Hello all,

I am using Oracle 10.2.0.5.0 and have written the following query which gives me the results I expect, just not in the format I want. The output is this:

Log_ID     1stPrimary          2ndPrimary          3dPrimary
123        JONES, MICHAEL      NULL                NULL
123        NULL                SMITH, DAVID        NULL
123        NULL                NULL                CLARK, JONATHAN
456        DAVIS, JIN          NULL                NULL
456        NULL                WONG, JIWEI         NULL

What I'd like to see is:

Log_ID     1stPrimary          2ndPrimary          3dPrimary
123        JONES, MICHAEL      SMITH, DAVID        CLARK, JONATHAN
456        DAVIS, JIN          WONG, JIWEI         NULL

For log 456, there are only 2 primaries so the last field can be NULL. Here is my query. Is there a simple step I am missing to return just one row per Log ID?

SELECT 
LOG_ID, 
MAX(CASE WHEN R = 1 THEN PROV_NAME END) AS "1stPrimary", 
MAX(CASE WHEN R = 2 THEN PROV_NAME END) AS "2ndPrimary",
MAX(CASE WHEN R = 3 Then Prov_Name End) as "3rdPrimary",
TITLE
From (Select Or_LOg.Log_ID, Prov_Name, Zc_Or_Panel_Role.Name Title, Row_Number() Over (Partition by Or_Log.Log_Id Order by Prov_Name) R
    From Or_Log, Or_Log_All_Surg Sur, Clarity_Ser, Zc_Or_Panel_Role, Or_Log_All_Proc, Zc_Or_Service
    Where Or_Log.Log_Id = Sur.Log_Id(+)
    And Sur.Surg_Id = Clarity_Ser.Prov_Id(+)
    And Sur.Role_C = Zc_Or_Panel_Role.Role_C(+)
    And Or_Log.Log_Id =  Or_Log_All_Proc.Log_Id(+)
    And Or_Log.Service_C =  Zc_Or_Service.Service_C(+) 
    And (Zc_Or_Panel_Role.Role_C <> 70 Or Zc_Or_Panel_Role.Role_C Is Null)
    And  Or_Log_All_Proc.Line  =  1
    AND ZC_OR_SERVICE.SERVICE_C  NOT IN  ('1000','30','999','230','350')
    AND ZC_OR_PANEL_ROLE.NAME = 'Primary')
    Group by Log_Id, Prov_Name, Title order by log_id

Thank you all.

 

[elsenorjose]

Figured it out. Did a quick rewrite of the query to change my grouping in the main query and redid the Row_Number ordering in the subquery and I get the results I want in one row per Log Id.

SELECT 
LOG_ID, 
MAX(CASE WHEN R = 1 THEN PROV_NAME END) AS "1stPrimary", 
MAX(CASE WHEN R = 2 THEN PROV_NAME END) AS "2ndPrimary",
MAX(CASE WHEN R = 3 THEN PROV_NAME END) AS "3rdPrimary",
TITLE
FROM (SELECT OR_LOG.LOG_ID, PROV_NAME, ZC_OR_PANEL_ROLE.NAME TITLE, ROW_NUMBER() OVER (PARTITION BY OR_LOG.LOG_ID ORDER BY OR_LOG.LOG_ID) R
    FROM OR_LOG, OR_LOG_ALL_SURG SUR, CLARITY_SER, ZC_OR_PANEL_ROLE, OR_LOG_ALL_PROC, ZC_OR_SERVICE
    WHERE OR_LOG.LOG_ID = SUR.LOG_ID(+)
    AND SUR.SURG_ID = CLARITY_SER.PROV_ID(+)
    AND SUR.ROLE_C = ZC_OR_PANEL_ROLE.ROLE_C(+)
    AND OR_LOG.LOG_ID =  OR_LOG_ALL_PROC.LOG_ID(+)
    AND OR_LOG.SERVICE_C =  ZC_OR_SERVICE.SERVICE_C(+) 
    AND (ZC_OR_PANEL_ROLE.ROLE_C <> 70 OR ZC_OR_PANEL_ROLE.ROLE_C IS NULL)
    AND  OR_LOG_ALL_PROC.LINE  =  1
    AND ZC_OR_SERVICE.SERVICE_C  NOT IN  ('1000','30','999','230','350')
    AND ZC_OR_PANEL_ROLE.NAME = 'Primary')
GROUP BY LOG_ID, TITLE

Oh, and sorry for the nasty mixed case code earlier. I should be more careful with the copy and paste