sql server - How to SUM distinct values Pivot Table

Question

I have data like this

... REF  R_UNITS  R_STOCK  COMPONENT  C_UNITS  C_STOCK  HELPER...
.................................................................
... 1    800        0        CODE1      800      500       1.....  
... 1    200        0        CODE1      200      500       1.....  
... 1    300        0        CODE1      300      500       1.....     
... 2    500      1000       CODE1      500      500       1.....   
... 2    200      1000       CODE1      200      500       1.....   
... 3    300       500       CODE1      300      500       1.....  
... 3    500       500       CODE1      500      500       1.....  
... 3    200       500       CODE1      200      500       1.....   
... 4    400        0        CODE1      400      500       1.....   
... 4    100        0        CODE1      100      500       1.....
.................................................................

And I want to pivot it like this:

COMPONENT  R_UNITS  R_STOCK  Σ C_NEEDED  C_STOCK   Σ C_TO_BUY
CODE1        3500     1500       2000      500        1500
CODE2        ...      ...         ...      ...         ...
CODE3        ...      ...         ...      ...         ...
....... 

I'd need to SUM DISTINCT by R_STOCK.

Data comes from a query in SQL-Server. I tried to group by REF and COMPONENT but it has so many sub-selects that I don't know how to.

My EXCEL doesn't support PowerPivot. Sadly, DAX is not an option

Is there any way to pivot the table the way I want by a calculated field?

EDIT: I can add new columns with SQL or M if needed

Thank you very much

Answer 1

Ok, solved. I grouped REF, COMPONENTS, etc. using PowerQuery.