Transpose in Oracle

Transposing data means changing data from a row into a column. Starting from version 11, this is possible in Oracle as well. It is possible to translate some values that appear in rows into columns. Doing so, a new table can be created that has an additional set of columns with column names being derived from row values. An example might clarify this:

create table ff
as
select * from (
   select 
   times_purchased as "Puchase Frequency", 
   state_code
   from customers t
)
pivot 
(
   count(state_code)
   for state_code in ('NY' as "New York",'CT' "Connecticut",'NJ' "New Jersey",'FL' "Florida",'MO' as "Missouri")
)
order by 1

In this examples a table (ff) is created. It is created from another table, customers. From that table two columns are retrieved: a field “times_purchased” and a field “state_code”. The latter field is translated into columns. The state_code is translated into five columns: New York, Connecticut etc. Whenever a value ‘NY’ is encountered, the column ‘New York’ is updated.
The update is defined under the key word “pivot”. Whenever a set of values in encountered, that is entered in another set, the count is incremented by 1; otherwise it is started at 1.

Let us look at the customer table. It looks like:

"STATE_CODE"	"TIMES_PURCHASED"
"NY"	100
"NY"	150
"NY"	100
"NY"	75
"CT"	75

Here, the state code values will be used to create columns. We will have a New York column and a Connecticut column.
Every record will generate an update. In principle the update will be according to a count: an increment by one if the row already exists, otherwise a 1. The first record will generate a record having 100 as times_purchased value and 1 in the Nuew York column. The second record will generate a record having 150 in the times_purchased and 1 in the New York column. Th third record will update the first record (100,1,0) to (100,2,0). The fourth record will generate another record. The fifth record will update an existing record from (75,1,0) to (75,1,1). The end result is:

"Puchase Frequency"	"New York"	"Connecticut"	
75      1       1	
100     2       0	
150     1       0