Pivot query with dynamic number of columns

Yes, we need to have dynamic PIVOT here...

Try the below:

Create Table SalesTransaction ( ProductType  varchar(15)   ,
                                SalesRegion  varchar(15)   ,
                                Amount       decimal(18,2) )
Insert Into  SalesTransaction Values ( 'Car'     , 'USA'      , 2000 )
Insert Into  SalesTransaction Values ( 'Car'     , 'Canada'   , 3000 )
Insert Into  SalesTransaction Values ( 'Car'     , 'Australia', 4000 )
Insert Into  SalesTransaction Values ( 'Truck'   , 'USA'      , 5000 )
Insert Into  SalesTransaction Values ( 'Truck'   , 'Canada'   , 6000 )
Insert Into  SalesTransaction Values ( 'Truck'   , 'Australia', 7000 )
Insert Into  SalesTransaction Values ( 'Aircraft', 'USA'      , 8000 )
Insert Into  SalesTransaction Values ( 'Aircraft', 'Canada'   , 9000 )

 DECLARE @query VARCHAR(4000) 
 DECLARE @Categories VARCHAR(2000) 
 SELECT  @Categories = STUFF(( SELECT DISTINCT                        
 '],[' + ProductType
 FROM    SalesTransaction A
 ORDER BY '],[' + ProductType
 FOR XML PATH('')                         ), 1, 2, '') + ']' 
 
  
 SET @query = 'SELECT * FROM ( SELECT *
 FROM SalesTransaction )t PIVOT (SUM(amount) FOR ProductType IN ('+@Categories+')) AS pvt' 
 --Print @Query
 EXECUTE (@query)

Drop table SalesTransaction