Merge multiple lines into one-CodePudding

I have a large dataset (1.000.000 lines) from witch I want to merge multible lines based on a key.

To make things clear, I've made a minimal viable product:

First some test-data

declare @TmpTable table
(
   Pnr varchar(10),
   Status varchar(10),
   Komkod  varchar(10),
   Fornvn varchar(10)
)

insert into @TmpTable values
('01010101', '01', null, null ),
('01010101', null , '0430', null ),
('01010101', null ,  null, 'Test' ),

('02020202', '10', null, null ),
('02020202', null, '3004', null ),
('02020202', null ,  null, 'Test' )

Then the output:

I want to merge the 6 lines into 2.

I've tried diffrent things (Windowed function, Inner join) but never found a good solution.

So now I need some good ideas. Perforce is king.

CodePudding user response：

You can simply aggregate which automatically ignores nulls:

select pnr,
  Max(status) Status,
  Max(Komkod) Komkod,
  Max(Fornvn) Fornvn
from @TmpTable
group by Pnr;