Skip to Content

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”

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Harish Allachervu

    Informative approach thanks for sharing 🙂 I tried to create similar scenario in my local system but unable to assign constant values for subject field, since I’m working on SP12 anything specific to this version?

    Ok button became inactive don’t know whats problem!!

    Regards,

    Harish

    (0) 
  2. Abhishek Agrawal Post author

    Hi Harish,

    Thanks for appreciating the efforts. Regarding your issue, I don’t think it has anything to do with HANA version, while checking your issue even I faced similar problem, so I deleted Subject and Marks columns and re-created Subject column and this time I could assign constant values.

    I tried to replicate the issue again but next time I could not replicate, it seems like a bug in studio, I would request you to try again and in case if you still face the same issue try some different options like deleting all target fields and adding Subject field first or delete Subject field and recreate it.

    I know this is not a solid solution but I really hope this should work. I would really appreciate if you can share what worked for you so that if others face the same issue they can follow your approach.

     

    Regards,

    Abhishek

     

     

    (0) 
    1. Harish Allachervu

      I appreciate for your swift reply, sorry for delay stuck up with some other work. I tried all ways of deleting individual and all fields at target still no luck 🙁 but if I maintain only 4 characters then Ok button becoming active, I’m working on sandbox system may be some patches are missing!! anyway this is really cool scenario learned so many things 🙂 keep sharing your knowledge to community.

       

      Regards,

      Harish

       

      (0) 

Leave a Reply