我想使用join和dynamic将一些数据转换为SQL Server中的pivot

最后,这张桌子是我的目标。

这是我的演示数据库

create database pvtestDb;
go

use pvtestDb;
go

create table custTransaction
(
    id int,
    custNum int,
    value nvarchar(50)
)
go

create table customers
(
    id int,
    custName nvarchar(50)
)

insert into Customers(id, custName) 
values (1, 'aaa'), (2, 'bbb'), (3, 'ccc'), (4, 'ddd'), 
       (5, 'eee'), (6, 'fff'), (7, 'ggg'), (8, 'hhh'), (9, 'iii')

insert into custTransaction (id, custNum, value) 
values (1, 3, 'a'), (1, 4, 'b'), (1, 5, 'c'),
       (2, 3, 'd'), (2, 4, 'e'), (2, 6, 'f'),
       (3, 3, 'g'), (3, 8, 'h'), (3, 9, 'i')

select * from customers
select * from custTransaction


select custName, custNum, value 
from customers
join custTransaction on custTransaction.id = customers.id

我尝试过这样的代码,但根本不起作用

SELECT 
    custNum, [a], [b], [c], [d]  
FROM
    customers
JOIN
    custTransaction ON custTransaction.id = customers.id
PIVOT
    (COUNT([custName]) 
     FOR [custName] IN ([a], [b], [c], [d])) AS p

我需要先连接两个表。

如果有任何提示,我将不胜感激