Post reply

How do I make this one row per id

  • July 16, 2025 at 8:38 pm

    #4623493

    I've written this code, but it is showing right now as 2 rows per invg_id and there are alot of nulls, i would like to bring the elements of the sup and agent into 1 row per invg_id.  how would i rewrite this?

    select f.INVG_ID, case when f.COM_TYPE = 28 then f.MaxCmtInvgDt end as 'MaxAgentSFRDt',

    case when f.COM_TYPE = 28 then f.CmtAddedBy end as 'MaxAgentSFRAddedBy',

    case when f.COM_TYPE = 28 then c.COM_DETAILS end as 'AgentSFRComment',

    case when f.COM_TYPE = 29 then f.MaxCmtInvgDt end as 'MaxSupSFRDt',

    case when f.COM_TYPE = 29 then f.CmtAddedBy end as 'MaxSupSFRAddedBy',

    case when f.COM_TYPE = 29 then c.COM_DETAILS end as 'SupSFRComment'

    from RPT_OBJ_PRD.RPT.RO_CMT_FACT f

    join OIGES_TRAN_PRD.IM.COMMENTS c

    on c.com_id = f.COM_ID

    where f.COM_TYPE in (28, 29)

    and f.MaxCmtInvg = 1

    order by f.INVG_ID desc

  • July 17, 2025 at 5:25 am

    #4623622

    Without data to test, maybe this will do the trick

    SELECT    f.INVG_ID
            , MaxAgentSFRDt      = MAX(CASE WHEN f.COM_TYPE = 28 THEN f.MaxCmtInvgDt END)
            , MaxAgentSFRAddedBy = MAX(CASE WHEN f.COM_TYPE = 28 THEN f.CmtAddedBy END)
            , AgentSFRComment    = MAX(CASE WHEN f.COM_TYPE = 28 THEN c.COM_DETAILS END)
            , MaxSupSFRDt        = MAX(CASE WHEN f.COM_TYPE = 29 THEN f.MaxCmtInvgDt END)
            , MaxSupSFRAddedBy   = MAX(CASE WHEN f.COM_TYPE = 29 THEN f.CmtAddedBy END)
            , SupSFRComment      = MAX(CASE WHEN f.COM_TYPE = 29 THEN c.COM_DETAILS END)
    FROM      RPT_OBJ_PRD.RPT.RO_CMT_FACT AS f
    JOIN      OIGES_TRAN_PRD.IM.COMMENTS  AS c ON c.com_id = f.COM_ID
    WHERE     f.COM_TYPE IN ( 28, 29 )
      AND     f.MaxCmtInvg = 1
    GROUP BY  f.INVG_ID
    ORDER BY  f.INVG_ID DESC;

    How to post data/code on a forum to get the best help.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply