Generating Columns Dynamically in HANA Calculation View

[Former Member]

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

[Former Member]

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

[Former Member]

Hello Avinash,

Thanks for your reply.

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

In the above example, We have distinct values as A and B. For my use case, the values in Column can be any number of distinct values.

I need to convert all those values in terms of column wwith their associated measure.

It will be some procedure or some module which on executing checks the number of distinct values in Column 2 and create those many columns in output with their associated measures.

I think above can be done if we can generate table types dynamically. I am not really sure how to achieve this.

Please suggest.

Thanks and Regards,

Tarun

[Former Member]

Hi Varun,

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

Awaiting your response

Thanks and regards

Avinash

[eralper_yilmaz]

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,

[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