SQL Server 2022

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

select @@version;


(No column name)


Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
 Oct  8 2022 05:58:25 
 Copyright (C) 2022 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table invoices ("Invoice ID" int, "Line Type" varchar(255),
  "Invoice Line Number" int, "PO Line Number" int, SUM int);
insert into invoices values
   (123, 'Non-Catalog Item', 1, NULL, 54000),
   (123, 'CGST', 2, NULL, 100),
   (123, 'IGST', 3, NULL, 50),
   (456, 'Catalog Item', 1, 2, 155000),
   (456, 'CGST', 2, 2, 13500),
   (456, 'IGST', 3, 2, 1040);
6 rows affected

select * from invoices;


Invoice ID
Line Type
Invoice Line Number
PO Line Number
SUM


123
Non-Catalog Item
1
null
54000

123
CGST
2
null
100

123
IGST
3
null
50

456
Catalog Item
1
2
155000

456
CGST
2
2
13500

456
IGST
3
2
1040


with agg as (
  select "Invoice ID",
     sum(case when "Line Type" = 'Non-Catalog Item' or "Line Type" = 'Catalog Item'
           then SUM else 0 end) as SUM,
     sum(case when "Line Type" = 'CGST' then SUM else 0 end) as CGST,
     sum(case when "Line Type" = 'IGST' then SUM else 0 end) as IGST
  from invoices
  group by "Invoice ID"
)
select * from agg;


Invoice ID
SUM
CGST
IGST


123
54000
100
50

456
155000
13500
1040


with agg as (
  select "Invoice ID",
     sum(case when "Line Type" = 'Non-Catalog Item' or "Line Type" = 'Catalog Item'
           then SUM else 0 end) as SUM,
     sum(case when "Line Type" = 'CGST' then SUM else 0 end) as CGST,
     sum(case when "Line Type" = 'IGST' then SUM else 0 end) as IGST
  from invoices
  group by "Invoice ID"
)
select inv."Invoice ID", inv."Line Type", inv."Invoice Line Number",
  ag.SUM, ag.CGST, ag.IGST
from invoices as inv
left join agg as ag
  on ag."Invoice ID" = inv."Invoice ID"
where "Invoice Line Number" = 1;


Invoice ID
Line Type
Invoice Line Number
SUM
CGST
IGST


123
Non-Catalog Item
1
54000
100
50

456
Catalog Item
1
155000
13500
1040

Invoice ID	Line Type	Invoice Line Number	PO Line Number	SUM
123	Non-Catalog Item	1	null	54000
123	CGST	2	null	100
123	IGST	3	null	50
456	Catalog Item	1	2	155000
456	CGST	2	2	13500
456	IGST	3	2	1040