Skip to Content
avatar image
Former Member

Generating Columns Dynamically in HANA Calculation View

Hello ,

We have a requirement to create columns dynamically in calculation view.

Suppose I have a calculatioView which exposes data as

Col1 Col2 Measure1

ID1 A 10

ID2 B 20

ID3 A 30

We want output as

Col1 Col2 Measure1 A B

ID1 A 10 10 0

ID2 B 20 0 20

ID3 A 30 30 0.

And Values in Col2 are not fixed. All distinct values in Col2 needs to be transformed in columns.

Can you suggest a way to do this.

Also is it suggested to change the xml generated using calculation views. If we create calculated columns in calculation view by changing genrated xml using JAVA code.

Is is guranteed that the semantics of xml structure will not change in upcoming releases of HANA.

Please suggest.

Thanks and Regards,

Tarun

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jan 04 at 07:59 AM

    Hello Tarun,

    What you actually want to implement is creating a dynamic SQL Pivot query. As a database programmer, I could implement this type of solutions on SAP HANA database as well as on other databases.

    If it was not dynamic you could simply implement SQL Pivot on HANA database by using CASE statements on additional SELECT fields.

    Following dynamic SQL script prepares finally a SQL statement which we could prepare, if we did not have the requirement of dynamic query.

    Below query will display the desired output, but actually we need an AMDP on native HANA procedure. I am pretty sure we cannot implement this solution just by using a view

    declare sqlstr nvarchar(4000);
    declare sqldyn nvarchar(4000);
    
    with cte as (
    select distinct Col2 from v_pivot1
    )
    select
        string_agg( 'case when Col2 = ''' || Col2 || ''' then Measure1 else 0 end as ' || Col2 || '', ',')
        into sqldyn
    from cte;
    
    sqlstr := 'select
        Col1,
        Col2,
        Measure1, ' || sqldyn || '
    from v_pivot1;
    ';
    
    execute immediate :sqlstr;
    
    

    The produced output is as seen in following screenshot

    I hope it helps,

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hello Eralper,

      Thanks for your reply.

      The real challenge I am facing is generating the new measures dynamically.

      Can you please suggest if it should be advisable if we change calculation view xml and add xml tags dynamically based on distinct values in column 2.

      Please suggest.

      Thanks and Regards,

      Tarun

  • avatar image
    Former Member
    Jan 04 at 12:20 PM

    Hi Tarun you can achieve the requiered output in 2 ways

    • 1.Creating a restricted column
    • 2.Creating a calculated column
    Please see below for the required solution

    1. Creating a restricted column

    Or You can use the second oprion of creating Calculated Column with the below syntax

    You will have to do the same for B.

    Hope this will help

    Regards,

    Avinash Thakur

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Varun,

      Did you found any solution ? I have also got similar requirement.

      Awaiting your response

      Thanks and regards

      Avinash