Pivots in IViews

Pivots are very useful to display as rows into columns. This is done by giving a column that has its column name as Pivot. All rows that have the same value in this column will be considered as one group. For each group one row will be displayed in the resultant IView. The columns to the left of the pivot column will be filled as it is. The columns to right of the pivot column are considered as value columns that will be repeated for group value.

Consider an example as below. The SQL of the IView gives the following result

Sales man Pivot Qty sold
Vishal Washing machine 5
Vishal Refrigirators 3
Vishal Stoves 6
Vishal Ovens 10
Sana Washing machines 3
Sana Ovens 4
Ravi Refrigerators 6
Ravi Stoves 8
Ravi Ovens 10
Ravi Washing machines 4


The IView result will be as below

Sales man Washing machine Refrigirators Stoves Ovens
Vishal 5 3 6 10
Sana 3 4
Ravi 4 6 8 10


The column values that are before the pivot column are called the key columns. The IView should be ordered by the key columns. In the above example if Sana appears after Jack and after Vishal, the resultant IView will have 2 rows for Sana.

You can bring 2 columns under each product like Qty sold and value sold.

Sales man Pivot Qty Sold Value
Vishal Washing machine 5 55000
Vishal Refrigirators 3 38000
Vishal Stoves 6 72000
Vishal Ovens 10 100000
Sana Washing machines 3 30000
Sana Ovens 4 45000
Ravi Refrigirators 6 63000
Ravi Stoves 8 89000
Ravi Ovens 10 101000
Ravi Washing machines 4 49000


The resulting IView will be as below.

Sales man Washing machineQty                  Value   RefrigeratorsQty          Value    StovesQty     Value    OvensQty    Value
Vishal 5 55000 3 38000 6 72000 10 100000
Sana 3 30000 4 45000
Jack 4 49000 6 63000 8 89000 10 101000


To divide the heading line based on data into 2 lines use the Carat symbol in data. For example if you want to display Washing machines as “Washing” and “machines in next line, in the pivot column where ever washing machine comes give it as “Washing^machines”. This will split the data into 2 heading lines.