Unpivot Data In HANA Using a Graphical Calculation View

[abhishek_agrawal9]

In my other blog I have covered how to PIVOT data in HANA, in this blog I am covering how to UNPIVOT data in HANA.

UNPIVOT data is a common requirement especially when we try to covert MS SQL query into SAP HANA data model. I ran into one such requirement, we had ETL data from MS SQL server to SAP HANA Enterprise and while converting one MS SQL model into HANA model I came across a Select query in MS SQL server where they were using UNPIVOT in Select statement.

I am using a simple example to explain how we can UNPIVOT data in HANA using Graphical Calculation View.

Here is the requirement:

Initial Data Set in HANA:



Output: UNPIVOT Data Set:



Pre-Requisite

Table in HANA, for this example I have created a student table in HANA:



 

Process:

Create a graphical calculation view. For this use case I have created a Dimension type Graphical Calculation view:



Add 3 projection nodes and add Student table to each of these projections. In this example we need to UNPIVOT 3 columns (ENGLISH, PHYSICS, MATHS) hence I have used 3 projection nodes, however if you have 2 columns to UNPIVOT then you need to use 2 projection nodes, in short Projection nodes = No. of columns you want to UNPIVOT.



Add a Union Node and connect all three projections to Union Node.



Now in mapping section of UNION node do the following:

• Click on Auto Map by Name

• Remove all the Target fields except STUDENT_ID

• Create two new Target columns Subject and Marks

 



 



In this step we will convert Column names ENGLISH, MATHS, and PHYSICS into column value.

Click on Manage Mapping of SUBJECT field and add constant values ‘English’, ‘Maths’, ‘Physics’ corresponding to three projection nodes. In MS SQL, UNPIVOT statement automatically converts Column header into Column values however in HANA we need to create constant values to achieve the same functionality.





Now we need to map marks/data of ENGLISH, MATHS and PHYSICS to newly created Marks target field.



Once UNION node mapping is done, connect UNION node to Projection node, add fields to output and activate the view:



Here is the output of the View



 

Just in case you want SQL version of same concept, then here is the corresponding SQL Query:

Select student_id as student, 'English' as subject, english as marks from         ABHISHEAGRAW"."abhisheagraw::STUDENT"
union all
Select student_id as student, 'Maths' as subject, maths as marks from "ABHISHEAGRAW"."abhisheagraw::STUDENT"
union all
Select student_id as student, 'Physics' as subject,physics as marks from "ABHISHEAGRAW"."abhisheagraw::STUDENT"