Combine two columns so data fits on one row?

1

Hi just wondering if it is possible to combine two columns into one to remove duplicate lines of data.

SELECT 
  events.name, 
  name.name2, 
  events.Effect_dt, 
  events.Comments, 
  events.Rate, 
  CASE WHEN events.Comments = 'RATE SET' THEN 0 ELSE events.Amount END as Amount, 
  sum(
    CASE WHEN events.Comments <> 'RATE SET' THEN - events.Amount ELSE 0 END
  ) over(
    partition by events.name 
    order by 
      events.Effect_dt
  ) as Rolled_Amount 
FROM 
  events events 
  left join .deals deals on events.name = deals.name 
  left join .name name2 on deals.name = name.thekey 
WHERE 
  events.comments not in ('INTEREST CALC') 
  and events.effect_dt >= '2024-01-01' 
  and events.effect_dt <= '2024-01-31' 
  and events.name = 'James'

Capture11

3

Welcome to forum @TaskMaster

Which of the rows you are showing are duplicate? Also what would it look like combined?

4

Hi sorry for not being clear. I would like the amount and rate column being on one row as when one is populated the other isn’t and grouped by the date column as we have two lines for the date.

Thank you for your assistance.

5

how about this:

create table #t (name varchar(20), Name2 varchar(20), EffectDt date, Comments varchar(20), rate numeric(5,4), Amount numeric(10,2))

insert into #t values
('James','Walker','1/1/2024','RATE SET',5.1869, 0),
('James','Walker','1/1/2024','Principal',0,-2500),
('James','Walker','2/1/2024','RATE SET',5.1863, 0),
('James','Walker','2/1/2024','Repay Principal',0,12.50),
('James','Walker','3/1/2024','RATE SET',5.1869, 0),
('James','Walker','3/1/2024','Repay Principal',0,12.50)

select Name, Name2, EffectDt, Sum(Rate) as Rate, sum(Amount) as amount, Rolled_Amount
from (
SELECT
events.name,
events.name2,
events.EffectDt,
events.Comments,
events.Rate,
CASE WHEN events.Comments = 'RATE SET' THEN 0 ELSE events.Amount END as Amount,
sum(
CASE WHEN events.Comments <> 'RATE SET' THEN - events.Amount ELSE 0 END
) over(
partition by events.name
order by events.EffectDt
) as Rolled_Amount
from #t events) v
group by Name, Name2, EffectDt, Rolled_Amount