[SOLVED] Pivot from row to column Oracle SQL Developer

Issue

This Content is from Stack Overflow. Question asked by Roundup

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 NumberProduct IDExpiry Date
aa23434202/02/2025
aa23487910/06/2023
aa23415825/09/2022
xx45y12316/09/2023
jab7636417/10/2028
lk89021914/01/2024
fgp89221914/01/2024
fgp89234918/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 NumberProduct IDExpiry DateProduct IDExpiry DateProduct IDExpiry Date
aa23434202/02/202587910/06/202315825/09/2022
xx45y12316/09/2023
jab7636417/10/2028
lk89021914/01/2024
fgp89221914/01/202434918/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



Solution

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";


This Question was asked in StackOverflow by Roundup and Answered by Tim Biegeleisen It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.

people found this article helpful. What about you?