使用窗口函数根据“Package Type”按字母顺序排列结果：

WITH ranked_products AS (SELECT *,
       ROW_NUMBER() OVER (PARTITION BY ProductNo
                          ORDER BY PackageType asc)
                    AS row_number
FROM products
) select *
FROM ranked_products
WHERE
  row_number = 1

**注：**这不会排除只有UCC的相同产品编号。

这会给你你想要的输出。值得注意的是，这可能不是一个很好的解决方案，因为它不容易扩展-如果你在某个时候需要处理第三，第四，第五个包类型，它会很快变得混乱。你可能会想看看使用PIVOT。但如果你很高兴它只是两个包类型，这是简单的，它应该是明确的代码在做什么。

CREATE TABLE #data (ProductNo INT,Barcode INT,PackageType VARCHAR(3),Length INT, Width INT);

INSERT INTO #data VALUES
(19,    123456  ,'UPC', 6,  2),
(19,    789101  ,'UCC', 3,  4),
(20,    987568  ,'UPC', 2,  5),
(20,    869584  ,'UCC', 7,  8),
(21,    869584  ,'UPC', 8,  3);

WITH ucc AS 
(
    SELECT ProductNo, Barcode, PackageType, Length, Width 
    FROM #data 
    WHERE PackageType = 'UCC'
),
upc AS 
(
    SELECT ProductNo, Barcode, PackageType, Length, Width 
    FROM #data 
    WHERE PackageType = 'UPC'
)
SELECT COALESCE(ucc.ProductNo, upc.ProductNo) AS ProductNo,
    COALESCE(ucc.Barcode, upc.Barcode) AS Barcode,
    COALESCE(ucc.PackageType, upc.PackageType) AS PackageType,
    ucc.Length AS UCCLength,
    ucc.Width AS UCCWidth,
    upc.Length AS UPCLength,
    upc.Width AS UPCWidth
FROM ucc 
FULL OUTER JOIN upc ON upc.ProductNo = ucc.ProductNo;