How to group by invg_id instead of null lines

1

by comment type, i want to create new columns. but for each invg_id, it's giving me 2 lines. how do i make it 1 line per invg_id
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

2 

select 
    f.INVG_ID, 
    max(case when f.COM_TYPE = 28 then f.MaxCmtInvgDt end) as 'MaxAgentSFRDt',
    max(case when f.COM_TYPE = 28 then f.CmtAddedBy end) as 'MaxAgentSFRAddedBy',
    max(case when f.COM_TYPE = 28 then c.COM_DETAILS end) as 'AgentSFRComment',
    max(case when f.COM_TYPE = 29 then f.MaxCmtInvgDt end) as 'MaxSupSFRDt',
    max(case when f.COM_TYPE = 29 then f.CmtAddedBy end) as 'MaxSupSFRAddedBy',
    max(case when f.COM_TYPE = 29 then c.COM_DETAILS end) as 'SupSFRComment'
from RPT_OBJ_PRD.RPT.RO_CMT_FACT f
inner 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
group by f.INVG_ID 
order by f.INVG_ID desc
3

hi

hope this helps

create tables test data script 
-- Create the RO_CMT_FACT table
CREATE TABLE RO_CMT_FACT (
    COM_ID int PRIMARY KEY,
    INVG_ID int,
    COM_TYPE int,
    MaxCmtInvg int,
    MaxCmtInvgDt datetime,
    CmtAddedBy varchar(50)
);

-- Create the COMMENTS table
CREATE TABLE COMMENTS (
    COM_ID int PRIMARY KEY,
    COM_DETAILS varchar(255)
);

-- Insert sample data into COMMENTS
INSERT INTO COMMENTS (COM_ID, COM_DETAILS) VALUES
(1, 'Agent comment detail 1'),
(2, 'Supervisor comment detail 1'),
(3, 'Agent comment detail 2'),
(4, 'Supervisor comment detail 2');

-- Insert sample data into RO_CMT_FACT
INSERT INTO RO_CMT_FACT (COM_ID, INVG_ID, COM_TYPE, MaxCmtInvg, MaxCmtInvgDt, CmtAddedBy) VALUES
(1, 100, 28, 1, '2025-07-10 09:00', 'AgentA'),
(2, 100, 29, 1, '2025-07-11 10:30', 'SupA'),
(3, 101, 28, 1, '2025-07-12 14:00', 'AgentB'),
(4, 101, 29, 1, '2025-07-13 16:15', 'SupB');

select 
  f.INVG_ID,
  max(case when f.COM_TYPE = 28 then f.MaxCmtInvgDt end) as MaxAgentSFRDt,
  max(case when f.COM_TYPE = 28 then f.CmtAddedBy end) as MaxAgentSFRAddedBy,
  max(case when f.COM_TYPE = 28 then c.COM_DETAILS end) as AgentSFRComment,  
  max(case when f.COM_TYPE = 29 then f.MaxCmtInvgDt end) as MaxSupSFRDt,
  max(case when f.COM_TYPE = 29 then f.CmtAddedBy end) as MaxSupSFRAddedBy,
  max(case when f.COM_TYPE = 29 then c.COM_DETAILS end) as SupSFRComment
from 
   RO_CMT_FACT f join COMMENTS 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;

image
image1149×420 20 KB

4

hi

hope this helps

3 different ways of implementing this

Performance Tuning considerations etc :stuck_out_tongue_winking_eye:

SQL PIVOT and Alternatives