Spreading values across rows (or subsets of rows within a set of rows) was once cumbersome and difficult, but the invention of window functions (which have been around for a while now) have made it much easier. Here are some examples you can take and tweak to fit your needs.
Note: I created these examples in Netezza because you can reference column aliases created in the same select clause, which makes the examples much cleaner. But this logic works in any database that supports window functions, such as SQL Server, you just might have to copy and paste the formula to multiple places.
As basic as it gets, this is just spreading a value equally across rows. In this example each trade has freight which is being spread across the items:
select *, TOTAL_FREIGHT / count(*) over (partition by TRADE_ID) as ITEM_FREIGHT, PRICE - COST - ITEM_FREIGHT as NET_PROFIT from TRADE order by TRADE_ID, LINE_ID;
This is spreading a value across rows based on each row’s weight. In this case the weight is the proportion of each item’s cost to the total cost of its trade:
select *, COST / sum(COST) over (partition by TRADE_ID) as ITEM_COST_RATIO, TOTAL_FREIGHT * ITEM_COST_RATIO as WEIGHTED_ITEM_FREIGHT, PRICE - COST - WEIGHTED_ITEM_FREIGHT as NET_PROFIT from TRADE order by TRADE_ID, LINE_ID;