Search code examples
sqloracle-sqldeveloper

Pivoting rows into columns Oracle SQL

I am currently working on a table where the format of the table is something like this (all the columns are of type VARCHAR except the INSERTDATE column which is of type DATE):

INSERTDATE | ID   | PROPERTYNAME  | PROPERTYVALUE
----------------------------------------------
date1      | 1000 | ItemNumber    | 20.1A14
date1      | 1000 | ItemRev       | 2
date1      | 1000 | BarCodeNumber | 3854981
date2      | 1001 | ItemNumber    | 20.1B24
date2      | 1001 | ItemRev       | 1
date2      | 1001 | BarCodeNumber | 3856539

What I want to do is to convert all PROPERTYNAME column values into separate columns with all of their respective PROPERTYVALUE column values into their respective columns, something like this:

INSERTDATE | ID   | ItemNumber | ItemRev | BarCodeNumber
-------------------------------------------------------
date1      | 1000 | 20.1A14    | 2       | 3854981
date2      | 1001 | 20.1B24    | 1       | 3856539

I have been trying to solve this problem for days without any result. I looked up everything on Pivot on the internet but none of the examples match my own needs. I am not much familiar with Pivot in SQL so it would really be helpful if anyone can help me figure out how to use it to solve my problem.

Solution

  • If you know the columns you want, you can use conditional aggregation:

    select insertdate, id,
       max(case when PROPERTYNAME = 'ItemNumber' then propertyvalue end) as ItemNumber,
       max(case when PROPERTYNAME = 'ItemRev' then propertyvalue end) as ItemRev,
       max(case when PROPERTYNAME = 'BarCodeNumber' then propertyvalue end) as BarCodeNumber
from t
group by insertdate, id;

    If you don't know all the properties up-front, then you need to construct the query dynamically as a string and use execute immediate.