Pivot from row to column Oracle SQL Developer

Roundup 0
Viewed 18

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.

Customer Number Product ID Expiry Date
aa234 342 02/02/2025
aa234 879 10/06/2023
aa234 158 25/09/2022
xx45y 123 16/09/2023
jab76 364 17/10/2028
lk890 219 14/01/2024
fgp892 219 14/01/2024
fgp892 349 18/12/2025

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

Customer Number Product ID Expiry Date Product ID Expiry Date Product ID Expiry Date
aa234 342 02/02/2025 879 10/06/2023 158 25/09/2022
xx45y 123 16/09/2023
jab76 364 17/10/2028
lk890 219 14/01/2024
fgp892 219 14/01/2024 349 18/12/2025

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

pivot oracle sql
1 Answers

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";
Tim Biegeleisen0
Related