Oracle SQL. Simple Pivot

Question

This is my very first post and apologies if it has been answered elsewhere. I am looking to do a simple pivot of some sort via SQL. I have:

PersID    PersItem
1            apple 
1            orange
2            peach
2            mango
2            grape

I would like:

PersID        PersItem  PersItem  PersItem
1               apple   orange      null
2               peach     mango     grape

Edit: Assuming the # of PersItem is not fixed. And thank you kindly for the answers!

score 0 · Answer 1 · answered May 11 '21 at 19:19

If you want three columns, you can use conditional aggregation with row_number():

select persid,
       max(case when seqnum = 1 then persitem end),
       max(case when seqnum = 2 then persitem end),
       max(case when seqnum = 3 then persitem end),
from (select t.*,
             row_number() over (partition by persid order by persid) as seqnum
      from t
     ) t
group by persid;

However, I would not recommend creating a separate column for each item. You can just concatenate them together:

select persid,
       listagg(persitem, ',') within group (order by persitem) as items
from t
group by persid;

score 0 · Answer 2 · answered May 11 '21 at 19:47

If number of max item is fixed then you can use static pivot for that as below. If you want to define column aliases in output as you shared then use Query#2.

Schema and insert statements:

 create table mytable( PersID int,   PersItem varchar(50));
 
 insert into mytable values(1,'apple');
 insert into mytable values(1,'orange');
 insert into mytable values(2,'peach');
 insert into mytable values(2,'mango');
 insert into mytable values(2,'grape');

Query#1:

 select * from 
 (select PersID,persitem,row_number()over(partition by persid order by rownum) rn from mytable)
 pivot
 (
 max(persItem) for rn in (1,2,3)
 )

Output:

PERSID	1	2	3
1	apple	orange	<emnull</em
2	peach	mango	grape

Query#2:

 select PersID,"1" PersItem,"2" PersItem, "3" PersItem from 
 (select persid,persitem,row_number()over(partition by persid order by rownum) rn from mytable)
 pivot
 (
 max(persItem) for rn in (1,2,3)
 )

Output:

PERSID	PERSITEM	PERSITEM	PERSITEM
1	apple	orange	null
2	peach	mango	grape

db<fiddle here

Oracle SQL. Simple Pivot

2 Answers2