Oracle SQL query that deals with inner Joins and values

Question

    SELECT  sc.TAAC_SHARE_CLASS_ID,
    SCS.SHARE_CLASS_SID,
    SCS.REPORTING_DT,
    SCS.SHARE_CLASS_SNAPSHOT_SID, 
    SCS.DIST_UNMOD_30_DAY_YIELD_PCT, 
    SCS.DER_DIST_12_MO_YIELD_PCT, 
    SCS.DER_SEC_30_DAY_YIELD_PCT AS SCS_DER_SEC_30_DAY_YIELD_PCT, 
    SCS.DER_SEC_RESTATED_YIELD_PCT AS SCS_DER_SEC_RESTATED_YIELD_PCT
    FROM SHARE_CLASS sc 
    INNER JOIN PORTFOLIO P ON (P.PORTFOLIO_SID=SC.PORTFOLIO_SID)
    INNER JOIN SHARE_CLASS_SNAPSHOT SCS ON 
    (SCS.SHARE_CLASS_SID=sc.SHARE_CLASS_SID)
    WHERE SCS.REPORTING_DT = '24-JUL-17' AND P.PORTFOLIO_ID = 638;

I ran this query and got the following output : image

Here, instead of getting separate rows for the same TAAC_SHARE_CLASS_ID, I want to merge the outputs of same TAAC_SHARE_CLASS_ID.

For example, the first row with TAAC_SHARE_CLASS_ID = 000648 should have values for all the 4 columns :

SCS.DIST_UNMOD_30_DAY_YIELD_PCT, 
SCS.DER_DIST_12_MO_YIELD_PCT, 
SCS.DER_SEC_30_DAY_YIELD_PCT,
SCS.DER_SEC_RESTATED_YIELD_PCT.

Hence the first row should have values for those columns as 2.96,3.2972596, 7541.085263433, 7550.

The last 4 rows of my output are not really required, as we have now merged those data into first 4 rows correspondingly.

How can I alter this query to achieve the same? Please help.

Answer 1

I suggest you group your results by TAAC_SHARE_CLASS_ID column, and MAX() the remaining columns, something like this:

SELECT  sc.TAAC_SHARE_CLASS_ID,
        max(SCS.SHARE_CLASS_SID) as SHARE_CLASS_SID,
        max(SCS.REPORTING_DT) as REPORTING_DT,
        max(SCS.SHARE_CLASS_SNAPSHOT_SID) as SHARE_CLASS_SNAPSHOT_SID, 
        max(SCS.DIST_UNMOD_30_DAY_YIELD_PCT) as DIST_UNMOD_30_DAY_YIELD_PCT, 
        max(SCS.DER_DIST_12_MO_YIELD_PCT) as DER_DIST_12_MO_YIELD_PCT, 
        max(SCS.DER_SEC_30_DAY_YIELD_PCT) AS SCS_DER_SEC_30_DAY_YIELD_PCT, 
        max(SCS.DER_SEC_RESTATED_YIELD_PCT) AS SCS_DER_SEC_RESTATED_YIELD_PCT
FROM SHARE_CLASS sc 
INNER JOIN PORTFOLIO P ON (P.PORTFOLIO_SID=SC.PORTFOLIO_SID)
INNER JOIN SHARE_CLASS_SNAPSHOT SCS ON (SCS.SHARE_CLASS_SID=sc.SHARE_CLASS_SID)
WHERE SCS.REPORTING_DT = '24-JUL-17' AND P.PORTFOLIO_ID = 638
GROUP BY sc.TAAC_SHARE_CLASS_ID;