您能否幫我撰寫選擇查詢以從下圖中獲得預期的輸出。我想要的是分別根據 UOM 列值 ZF2、ZF1 和 ZF3 將 UPC 列值拆分為三列（瓶 UPC、包裝 UPC、案例 UPC）。
任何幫助，將不勝感激。謝謝！
uj5u.com熱心網友回復：
您可以使用三個查詢來分隔列并使用 UNION ALL 將它們連接起來。然后我們使用 max 只保留我們想要的那個。
我知道這不完全是您顯示的格式，但它確實包含了所有資訊而沒有重復。
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,
uj5u.com熱心網友回復：
這是獲得您想要的確切結果的一種方法，但令人困惑的是為什么您希望所有三行都回傳您已經轉置到第一行的值...
;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;
- 示例db<>fiddle
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/457454.html