Pivoting rows into columns Oracle SQL

Question

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.

Gordon Linoff · Accepted Answer · 2020-10-06 15:53:55Z

1

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.

answered Oct 6, 2020 at 15:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sujitmohanty30 · Accepted Answer · 2020-10-06 17:55:49Z

0

The use case is a right candidate for PIVOT and you can use it perfectly here.

Assuming you are using Oracle as your database the query will look like,

select insertdate,id,ItemNumber,ItemRev,BarCodeNumber
  from mytable
pivot
(
 max(propertyvalue)
 for propertyname in ('ItemNumber' as ItemNumber
                     ,'ItemRev' ItemRev
                     ,'BarCodeNumber' BarCodeNumber)
)
order by insertdate;

answered Oct 6, 2020 at 17:55

Sujitmohanty30

3,3362 gold badges9 silver badges24 bronze badges

1 Comment

Sujitmohanty30 Over a year ago

@B Basak, as you specifically asked for PIVOT I was interested answering even there is an answer already available. Out of curiosity , did it not work with your use case ?

Collectives™ on Stack Overflow

Pivoting rows into columns Oracle SQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related