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!
CodePudding user response:
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
CodePudding user response:
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 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,