Convert sold quantity to standardized units in SQL-CodePudding

I have a result containing total sold quantity in different units. I need to bring it to standardized units based on reference table. This standardised units can vary from KG,LBS or GAL .

Quantity Sold :

Material	UnitOfMeasure	QuantitySold
1910	KG	446780
1910	GAL	5000
1911	GAL	2000

Reference :

Material	FromUnits	ToUnits	Multiplier
1910	GAL	Lbs	7.107
1910	Lbs	Kgs	0.5
1910	KG	Lbs	2.3
1911	GAL	Lbs	9.3
1911	Lbs	Kg	.9
1911	KG	Lbs	4.2

I Need :

Material	UnitOfMeasure	QuantitySold	Standardised_Lbs
1910	KG	446780	(446780*2.3)
1910	GAL	5000	(5000*7.1)
1911	GAL	2000	(2000 * 9.3)

CodePudding user response：

Seems like a Conditional Aggregation in concert with a JOIN should do the trick.

Select A.Material
      ,A.UnitOfMeasure
      ,A.QuantitySold
      ,Standardised_Lbs = sum( case when B.ToUnits = 'Lbs' then A.QuantitySold * B.Multiplier end )
      ,Standardised_KG  = sum( case when B.ToUnits = 'KG'  then A.QuantitySold * B.Multiplier end )
      ,Standardised_GAl = sum( case when B.ToUnits = 'GAL' then A.QuantitySold * B.Multiplier end )
 From  [QuantitySold] A
 Join  [Reference]    B on A.Material=B.Material 
                       and A.UnitOfMeasure = B.FromUnits
 Group By A.Material
         ,A.UnitOfMeasure
         ,A.QuantitySold