split one column values into multiple column based another column value

Question

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 1

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

Answer 2

You can use three queries to separate the columns and join them with UNION ALL. We then use max to only keep the one we want.
I'm aware that this is not exactly the format you showed but it does include all the information without duplication.

Select
  Material_id,
  UOM,
  Max(Bottle) as Bottle_UPC, 
  Max(Pack) as Pack_UPC, 
  Max(Case) as Case_UPC
From 
(
Select
  Material_id,
  UOM,
  UPC as Bottle, 
  '' as Pack, 
  '' as Case
From table_name
Where UOM = 'ZF1'
   Union all
Select
  Material_id,
  UOM,
  '' , 
  UPC , 
  '' 
From table_name
Where UOM = 'ZF2'
   Union all
Select
  Material_id,
  UOM,
  '' , 
  '' , 
  UPC 
From table_name
Where UOM = 'ZF3'
)
Group by
  Material_id,
  UOM,