I have table look like below. I have two type of package type for a single product. But some product has only UPC

But I need a loke like this table. Basically I want to keep a row only where package type = UCC when the product have two package type. But when there is only one package type ( e.g. product no 21) then I need to keep Package type= UPS too. Then I need to keep length and width for both package type.

I've been struggling how to do it in a SQL. If any help please it would be appreciated

CodePudding user response：

Use a window function to rank the results alphabetically based on "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

NOTE: This wouldn't exclude same Product No with only UCC.

CodePudding user response：

This gives you the output you want. Worth noting that its perhaps not a great solution in that its not easily extensible - if you at some point need to handle a third, fourth, fifth package type, its going to get messy fast. You would probably want to look into using PIVOT then. But if you are happy its just the two package types, this is simple and it should be clear what the code is doing.

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;