SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table invoices (
  InvoiceID	int,
  LineType	varchar(50),
  InvoiceNumber	int,
  PO int,
  SUM int
);
insert into invoices values 
(569052, 'Non-Catalog Item', 1, NULL, 13500), 
(569052, 'IGST', 2, NULL, 1341), 
(569052, 'Catalog Item', 3, NULL, 670700), 
(569052, 'CGST', 4, NULL, 1000), 
(569052, 'Non-Catalog Item', 5, NULL, 2000),
(569052, 'IGST', 6, NULL, 100), 
(569052, 'Non-Catalog Item', 7, NULL, 1000),
(569052, 'CGST', 8, NULL, 300); 
8 rows affected

with cte as (
  select *, sum(case when LineType in ('Non-Catalog Item', 'Catalog Item') then 1 else 0 end) 
            over (partition by InvoiceID order by InvoiceNumber) as grp,
            case when LineType in ('Non-Catalog Item', 'Catalog Item') then 1 else 0 end as catalog_flag
  from invoices
)
select InvoiceID, max(case when catalog_flag = 1 then LineType end) as LineType, 
                  max(case when catalog_flag = 1 then InvoiceNumber end) as InvoiceNumber, 
                  sum(case when catalog_flag = 1 then sum end ) as Amount,
                  sum(case when LineType = 'IGST' then sum end ) as IGST,
                  sum(case when LineType = 'CGST' then sum end ) as CGST
from cte c
group by InvoiceID, grp


InvoiceID
LineType
InvoiceNumber
Amount
IGST
CGST


569052
Non-Catalog Item
1
13500
1341
null

569052
Catalog Item
3
670700
null
1000

569052
Non-Catalog Item
5
2000
100
null

569052
Non-Catalog Item
7
1000
null
300


Warning: Null value is eliminated by an aggregate or other SET operation.

InvoiceID	LineType	InvoiceNumber	Amount	IGST	CGST
569052	Non-Catalog Item	1	13500	1341	null
569052	Catalog Item	3	670700	null	1000
569052	Non-Catalog Item	5	2000	100	null
569052	Non-Catalog Item	7	1000	null	300