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

This question is not yet answered, be the first one who answer using the comment. Later the confirmed answer will be published as the solution.

This Question and Answer are collected from stackoverflow and tested by JTuto community, 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?