fbpx

Columns to Rows (ETL)

Allows for transforming information available over several columns into rows.

The easiest way is to explain with an example :

COUNTRY, PRODUCT, SALES_2015, SALES_2014
Canada,  Bike ,   10,         20
Canada,  Car ,    20,         40
USA,     Bike ,   100,        200
USA,     Car,     500,        1000
France,  Bike ,   10,         20
Spain,   Car ,    100,        200
    

This view contains some product sales for the years 2015 and 2014. Those values are available via two columns (SALES_2015 and SALES_2014) which does not fit the expected model. We need a table with two other columns, 'year' and 'amount'. We're looking for a table that looks like :

Expected

COUNTRY, PRODUCT, YEAR, AMOUNT
Canada,  Bike ,   2015,         20
Canada,  Bike ,   2016,         40
...
    

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

Creating the transformation view

The first step is to create a "Columns To Rows" view and fill the fields.

Columns: contains the columns to transform: SALES_2014, SALES_2015

Index Column Name: this is the name of the new column that will contain the 'year' information of the sales. So let's call it 'YEAR'.

Index Column Type: the type of the values in the index column; if not specified the type is inferred from the "Index Column Content" either as string or integer.

Index Column Content: 'YEAR' content: 2014, 2015. They must be in the same order as "Columns".

Value Column Name: this is the name of the new column that will contain the 'sales' information. So let's call it 'AMOUNT'.

Eventually, we can browse the sales_columns_to_rows view will display (on hover icon besides the view name):

COUNTRY, PRODUCT, YEAR, AMOUNT
Canada,  Bike,    2014, 10,
Canada,  Bike,    2015, 20,
Canada,  Car,     2014, 20,
Canada,  Car,     2015, 40,
USA,     Bike,    2014, 100,
USA,     Bike,    2015, 200,
USA,     Car,     2014, 500
USA,     Car,     2015, 1000
France,  Bike,    2014, 10
France,  Bike,    2015, 20
Spain,   Car,     2014, 100
Spain,   Car,     2015, 200
    

Walkthrough

The schema mentioned in this page can be downloaded here.

Next chapter: Row to Columns describes how to perform row to Columns operations.