SQL Server: join data but in single row

I have a table Invoice that looks similar to this:

order_id	completed_at	subtotal	discount_amount	handling_amount
100	07/01/2021	10.09	0	0
101	07/04/2021	200.30	0	0
102	07/04/2021	54.10	0	0
103	07/06/2021	12.00	0	0

And I have another table InvoiceDetail:

order_id	product_id	qty	amount
100	1234	1	1.09
100	Shipping1	1	4.30
100	SalesTax	1	.67
101	987	2	74.20
101	654	1	6.20
101	Shipping2	1	5.10
101	SalesTax	1	2.30
102	123	1	30.15
102	Shipping8	1	6.80
102	SalesTax	1	1.08
103	321	1	8.04
103	Shipping4	1	2.05
103	SalesTax	1	.70

I need to join the tables to look like this:

order_id	completed_at	subtotal	shipping	sales_tax	discount_amount	handling_amount
100	07/01/2021	10.09	4.30	.67	0	0
101	07/04/2021	200.30	5.10	2.30	0	0
102	07/04/2021	54.10	6.80	1.08	0	0
103	07/06/2021	12.00	2.05	.70	0	0

I have tried a few different things but every time it ends up with multiple rows for each order_id.

SELECT 
    order_id,
    completed_at,
    subtotal,
    CASE 
        WHEN t2.product_id LIKE '%Shipping%'
            THEN t2.amount 
    END AS shipping,
    CASE 
        WHEN t2.product_id = 'SalesTax'
            THEN t2.amount 
    END AS sales_tax,
    discount_amount,
    handling_amount
FROM 
    Invoice t1
INNER JOIN 
    InvoiceDetail t2 ON t1.order_id = t2.InvoiceDetail
WHERE 
    order_id BETWEEN '100' AND '103'

The above will output the information but in multiple rows for each order_id. I need the order information combined with the shipping and salestax all in one row. How do I achieve what I’m after?

Answer

You can use conditional aggregation using CASE inside a SUM() aggregation function. For example:

select
  i.order_id,
  max(i.completed_at) as completed_at,
  max(i.subtotal) as subtotal,
  sum(case when d.product_id like 'Shipping%' then amount end) as shipping,
  sum(case when d.product_id = 'SalesTax' then amount end) as sales_tax,
  max(i.discount_amount) as discount_amount,
  max(i.handling_amount) as handling_amount
from invoice i
left join invoicedetail d on d.order_id = i.order_id
group by i.order_id