split one column values into multiple column based another column value

could you please help me writing select query to get expected output from the below picture. what I want is to split the UPC column values into three columns(Bottle UPC, Pack UPC, Case UPC) based on its UOM column values ZF2, ZF1 and ZF3 respectively.

any help would be appreciated. Thank you!

Answer

This is one way to get the exact result you want, but it is confusing why you’d want all three rows to be returned with values you’ve already transposed to the first row only…

;WITH cte AS 
(
  SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY Material_ID ORDER BY UOM),
    bupc = CASE WHEN UOM = 'ZF2' THEN UPC END,
    pupc = CASE WHEN UOM = 'ZF1' THEN UPC END,
    cupc = CASE WHEN UOM = 'ZF3' THEN UPC END
  FROM dbo.SomeTableName AS s
),
agg AS 
(
  SELECT Material_ID, 
    bupc = MAX(bupc), 
    pupc = MAX(pupc), 
    cupc = MAX(cupc)
  FROM cte GROUP BY Material_ID
)
SELECT cte.Material_ID, cte.UOM, 
    [Bottle UPC] = COALESCE(agg.bupc, ''),
    [Pack UPC]   = COALESCE(agg.pupc, ''),
    [Case UPC]   = COALESCE(agg.cupc, '')
  FROM cte
  LEFT OUTER JOIN agg ON agg.Material_ID = cte.Material_ID
  AND cte.rn = 1;

• Example db<>fiddle