Sql Query

If my understanding is correct, the value of Profitamont in the first line of the output should be 20.

To get the result you want, needs 3 steps:

1. Aggredated [SalesCode] by [Product] and [Trans_type].
2. Transform the values of [Trans_type] to columns by using PIVOT.
3. Summary the [Profitamount] by [Product] and JOIN the rowset with the rowset which returns by PIVOT.

Code

;
WITH CTE([Product], [Trans_type], [Codes], [Profitamount])
AS
(
	SELECT [Product], [Trans_type], STRING_AGG([SalesCode], ',') AS Codes, SUM([Profitamount]) AS [Profitamount] 
	FROM [Source_Table]
	GROUP BY [Product], [Trans_type]
),
P([Product], [sales_type1], [sales_type2])
AS
(
	SELECT [Product], [Type1], [Type2]  FROM 
	(
		SELECT [Product], [Trans_type], [Codes] FROM CTE 
	) AS S
	PIVOT
	(
		MIN([Codes]) FOR [Trans_type] IN ([Type1], [Type2])
	)AS PV
)
SELECT P.*, T.[Profitamount] FROM P LEFT JOIN
(
	SELECT [Product], SUM([Profitamount]) AS [Profitamount] FROM CTE
	GROUP BY [Product]
)T ON P.[Product] = T.[Product]

Are you sure you want the first row's profit amount to be 16, isn't it supposed to be 20. 

You could use the following query:

Select 
    product,
    STRING_AGG(case when Trans_type = 'Type1' then SalesCode end, ',') as sales_type1,
    STRING_AGG(case when Trans_type = 'Type2' then SalesCode end, ',') as sales_type2,
    sum(Profitamount) Profitamount 
from 
    [dbo].[Source_Table] 
group by 
    product