Hi I hope someone can please give me some guidance on this in Oracle SQL Developer

I have a table which has a customer number, product ID as well as an expiry date for each product ID.

What I would like to to do is transpose the table so all product IDs and Expiry Date rows are put into a separate column for each Customer Number. An example of the output is below

I would like this to be created for 7 instances. I.e some of the same customers might have 7 different Product IDs.

Can someone please recommend the best way of achieving this. I suspected perhaps the pivot function may help, or self joining to the same table where table1.Customer Number = Table2.Customer Number and Table1.Product ID <> Table2.Product ID. But I'm really just guessing.

I hope this all makes sense.

Thanks

CodePudding user response：

We can use ROW_NUMBER along with pivoting logic here:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY "Customer Number"
                                   ORDER BY "Expiry Date") rn
    FROM yourTable t
)

SELECT
    "Customer Number",
    MAX(CASE WHEN rn = 1 THEN "Product ID" END)  AS "Product ID 1",
    MAX(CASE WHEN rn = 1 THEN "Expiry Date" END) AS "Expiry Date 1",
    MAX(CASE WHEN rn = 2 THEN "Product ID" END)  AS "Product ID 2",
    MAX(CASE WHEN rn = 2 THEN "Expiry Date" END) AS "Expiry Date 2",
    MAX(CASE WHEN rn = 3 THEN "Product ID" END)  AS "Product ID 3",
    MAX(CASE WHEN rn = 3 THEN "Expiry Date" END) AS "Expiry Date 3",
    MAX(CASE WHEN rn = 4 THEN "Product ID" END)  AS "Product ID 4",
    MAX(CASE WHEN rn = 4 THEN "Expiry Date" END) AS "Expiry Date 4",
    MAX(CASE WHEN rn = 5 THEN "Product ID" END)  AS "Product ID 5",
    MAX(CASE WHEN rn = 5 THEN "Expiry Date" END) AS "Expiry Date 5",
    MAX(CASE WHEN rn = 6 THEN "Product ID" END)  AS "Product ID 6",
    MAX(CASE WHEN rn = 6 THEN "Expiry Date" END) AS "Expiry Date 6",
    MAX(CASE WHEN rn = 7 THEN "Product ID" END)  AS "Product ID 7",
    MAX(CASE WHEN rn = 7 THEN "Expiry Date" END) AS "Expiry Date 7"
FROM cte
GROUP BY "Customer Number";