Dynamic pivot tables?

Hi All,

Very new to sql so and are having a hard time trying to work out if this is possible, effectively i am trying to create a dynamic pivot table based on the below table data, with multiple columns based on how many products and adjuvants are in the treatment. Please view the image for the two tables i keep trying to add the query table and it keeps messing it up.

Proposed Pivot table result please see the pdf

I have no idea if this is even possible, i was thinking of creating multiple group queries and joining them on the treatment_id, ie based on the results of a count if its a product or adjuvant (confrim_adjuvant) and grouping based on how many there are, any help is greatly appreciated. Truly sorry about how difficult it is to read.

Thank you in advance.