1 – Definition
A pivot table is the result of the action that allows data tables to be rearranged in many ways for different views of the same data.
Technically, if we compare a table with a matrix we can think to the pivot table as a transposed matrix, where the original row can be read as a column in the resulting table.
2 – How to generate a pivot table
Immagine to have the following data set:
select practice, office, count(1) from organization group by practice, office
Now you want the office’s numbers to be columns. We have 4 offices (1,2,3,4) and we want to have the following columns in our resulting pivot table: OFF_1, OFF_2, OFF_3, OFF_4.
The query to generate the desired pivot table will look like the following:
select practice, max( decode( office, 1, cnt, null ) ) off_1, max( decode( office, 2, cnt, null ) ) off_2, max( decode( office, 3, cnt, null ) ) off_3, max( decode( office, 4, cnt, null ) ) off_4 from ( select practice, office, count(1) cnt from organization group by practice, office ) group by practice
CNT column has been pivoted by OFFICE across PRACTICE.
Remember that this works only if you know the domain of the column you pivote by.