Row to Columns (ETL)

Allows for transforming information available in one column to generate new columns

The easiest way is to explain with an example :

Canada,         2010, Health expenditure private (% of GDP), 2.6
Canada,         2010, Health expenditure public (% of GDP) , 6.1
United States,  2010, Health expenditure private (% of GDP), 7.4
United States,  2010, Health expenditure public (% of GDP) , 5.7

This view contains a columns, 'MEASURE', which values we would like to convert to new Columns. In our example, one for private and another for public health expenditure.


COUNTRY, YEAR, Health expenditure private (% of GDP), Health expenditure public (% of GDP)
Canada,         2010,    2.6,   6.1
United States,  2010,    7.4,   5.7

Let's explain how to create a transformation 'row to columns' to achieve this.

Creating the transformation view

The first step is to create a "Row to columns" view and fill the fields.

Row for columns: Row used to generate the multiple columns. For each distinct value on this column a new measure column is generated.

Measure columns: Measures columns. These columns will generate new columns that start with the measure column name and end with the values of the 'Row To Column' column. ( 'MeasureA Val1', 'MeasureA Val2'... )

New columns suffix names: New columns suffix names (should be equal to the sorted unique values of the Row to Column value). icCube can not generate new columns dynamically as we need to bind them statically , in the schema, so they can be used.

Table is already sorted: Table used by the view is already sorted. If yes, the view will not cache the whole table making it less memory consuming. Note, the table needs to be sorted by all columns except the ones defined by 'Measure columns' and 'Row to column'.

And it's done, you can click in browse table data, magnifier icon, to see the result in icCube.


The schema mentioned in this page can be downloaded here.

Next chapter: Group By describes how to perform some group by operations.