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 < 500, Category = “MEDIUM”
If the Quantity < 100, Category = “LOW”
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
Transposing Data using Calculation View
e.g. to add a column (ColumnA) in the resultset syntax to be used
select column1, column2, <constant value> as ColumnA from <table/view>;
SQL SCRIPT:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
9 | |
9 | |
5 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |