There are many nice articles available on hana modeling which talk about transposing data from columns to rows.

In this article we will see how to transpose data from rows to columns.

Case Study

Before going into the technical details, let’s take a case study.

In a table we have a product list with their selling quantities . Depending on the selling quantities, products need to be categorized and an aggregated report needs to be generated to show count of products falling in a specific category.

e.g.

If the Quantity > =500, Category = “HIGH”

If the Quantity >=100 and

If the Quantity

We need a report in following format

LOCATION

HIGH

MEDIUM

LOW

LOC1

2

2

1

LOC2

1

3

1

Analytic View for adding new column to the resultset

  • Create an analytic view and map all fields from the database table in the output.

  • Add a calculated Column “Category” in the analytic view. Write following expression to evaluate value for “Category” field.

  • After activating the analytic view we can see its data in data preview as below:

Transposing Data using Calculation View

  • Create a SQL Script type calculation view

  • In the calculation view, Add the target columns as required in the result of transpose(Location, Quantity for High , Quantity for Medium, Quantity for Low).

  • Now in the script we will need to write queries to populate the target resultset.
  • For each of the new column in the resultset we will write a separate query to fetch corresponsing information.
  • Since the analytic view doesn’t have these three columns we will use constants to populate the new columns added in the resultset.

e.g. to add a column (ColumnA) in the resultset  syntax to be used

select column1, column2, as ColumnA from

;
  • For each category(HIGH/MEDIUM/LOW) we will write a query to populate constant value ‘1’ for its respective column and ‘0’ for other columns.
  • Union all result sets and finally aggregate the result set to get the aggregated output.
  • Please see below the outputs of the queries and how they will merge into final output.

SQL SCRIPT:

  • After activating the calculation view, you will be able to see data in the transposed format.

New NetWeaver Information at SAP.com

Very Helpfull

User Rating: Be the first one !