How to pivot dataset in SAP HANA

[rey_atun]

Hi there,

I have a scenario (as shown below) in SAP HANA wherein I need to pivot a column table without using an ETL product like SAP Data Service.

Do you have any idea or suggestion on how to do this?

Thanks in advance for your help.

[brenden_kennedy · Accepted solution]

Hi Rey,

Understanding that you have a fixed maximum of 3 columns/value with any additional values to be ignored, you could use the following SQL syntax.

In terms of performance this should be suitable for small data sets. If you expect a high percentage of rows exceeding the 3 values (ie ROW_NUM > 3) you could also add a having clause to limit the ROW_NUM to 3 to reduce the inner data set and optimise performance.

I believe we cannot use "MAX" in the graphical modelling (I cannot find a way!) so I am looking at how to code this in CE functions however I am challenged to have the row_number function (Window Function) in a CE function. Does anybody know if (and have an example) we can use a Window Function within a CE function? I find limited documentation on the Window Functions implementation.

SQL sytanx

SELECT "OBJ_NO",

  MAX(CASE WHEN ROW_NUM=1 THEN "VALUE" ELSE NULL END) AS VALUE_1,

  MAX(CASE WHEN ROW_NUM=2 THEN "VALUE" ELSE NULL END) AS VALUE_2,

  MAX(CASE WHEN ROW_NUM=3 THEN "VALUE" ELSE NULL END) AS VALUE_3

FROM (

SELECT "OBJ_NO", "VALUE", row_number () over (partition by "OBJ_NO") as ROW_NUM

FROM SOURCETABLE

ORDER BY "OBJ_NO" )

GROUP BY "OBJ_NO";

Cheers

[Former Member · Accepted solution]

Window functions are not available in any graphical models. Also, and related - window functions (currently) execute in the row store, not the column store - so performance may suffer depending on data volume.

- I'd encourage you to read the link provided . You essentially are flattening out a parent-child hierarchy. This can be accomplished on-the-fly if modeled correctly (parent-child hierarchy in Attribute or Calculation View) with good performance. You'll need a front-end tool that can query via MDX like AAO.

Otherwise you'll have to flatten manually via an ETL-type process. Sounds like that's not an option for you though.

[Former Member · Accepted solution]

Hi,

What if we want to have a dynamic number of rows unlike 3 as specified in your code ?

can you please check on this.

[former_member182302]

Hello Rey,

Have a look on the below documents, Which explains the different approaches which can be used:

Regards,

Krishna Tangudu

[rey_atun]

Hi Krishna,

Thanks for the links above.

However, the requirement is the other way around - more columns (or fields) rather than rows (or records).

Regards,

-Rey

[former_member182302]

Hi Rey,

Have a look on the discussions in this thread:

Regardsm

Krishna Tangudu