Transpose a record in Oracle isn’t easy. I had a small table with several records and one value in a record. I wanted to transpose that table into one record with the values adjacent to each other. The question: how to accomplish this? Recently, Oracle introduced the pivot facility that allowed this procedure. The code to undertake is as follows:
select * from
(select peri_omsch,
ROW_NUMBER ()
over (PARTITION BY een order by peri_omsch) as rn
from (select distinct '1' as een, peri_omsch from salesall order by peri_omsch))
pivot
(
min(peri_omsch)
for rn in (1 as a1,2 as a2,3 as a3,4 as a4,5 as a5,6 as a6,7 as a7,8 as a8,9 as a9,10 as a10,11 as a11,12 as a12)
);
Let us start with this table:
The sql fetches the values from the original table (peri_omsch) and it adds an additional field that contains the row number. The code to add that additional field uses a ranking function where the whole table is seen as one set with a fixed value (‘een’) as an identifier for the set. Upon this set a rownumber is added.
We get
I use this rownumber to create the columns. Each value of the rownumber (1, 2, 3 etc ) is translated into a column. Hereby 1 is translated into column a1, 2 is translated into column a2 etc. I store the peri_omsch in this column. To enforce a unique value, I used the min function, which enforces one unique value to be stored.
The final result looks like:
