# Modelling: Column to Row Transpose using Matrix in HANA

##### Tags:

**Contributors:**

**Abani Pattanayak, SAP HANA COE **

**Tony Cheung, SAP Business Analytics Services**

# Background:

In almost every project, some form of data transformation is required. Most of these transformations are some combination of Aggregation, Projection, Union or Join steps and these transformation can be easily done in the HANA models.

Where possible, It may be a good idea to push the complex transformation to the data acquisition layer using ETL tools like data services.

In certain scenarios, we may need additional data transformation on top of transformed data (say. you want to transform the output of an Analytic or Calculation view), in this scenario ETL tool can't be used.

# Problem Statement:

There are two types of transformation

**Row to Column Transpose****Column to Row Transpose**

**1. Row to Column Transpose: **This can be easily done using **Restricted Measure** (available in Analytic View) and **Calculated Measures** (available in both Analytic View and Calculation View)

**2. Column to Row Transpose:**

Let's start with an example. The following is output of Analytic View (AN_DATA)

We'd like to transform the dataset (from column to row) as shown below

# Solution Options:

We'll use **matrix transpose** to achieve this result. Refer to the Wikipedia link for detail of this concept Transpose - Wikipedia, the free encyclopedia and Elementary matrix - Wikipedia, the free encyclopedia

1. Create Matrix table **DATA_TRANSFORM **as below. 3 columns for 3 measures in our source data and an extra column "dummy".

2. Insert the dataset as below to **DATA_TRANSFORM table**

3. Build the calculation view as below.

- Create a Projection (
**proj_DATA)**on the Analytic View (AN_DATA). Create a calculated column "DUMMY" with value 1 - Create a Projection (
**proj_MATRIX)**on the DATA_TRANSFORM table - Join
**proj_DATA**and**proj_MATRIX**on the**DUMMY field.**Create two calculated column**AMT_TYPE**and**AMT**as shown below - Expose the
**DOCID**,**COUNTRY**,**AMT_TYPE**and**AMT**in the Aggregation node.

**Result:**

Now execute the following Query for the desired output