Skip to Content
0

Transpose Data in SAP HANA

Nov 27, 2017 at 01:49 PM

29

avatar image
Former Member

Main Source Data:

TYPE  ID  VERSION      Column_NAME    VALUE
A   WXYZ    0            COLUMN_A       ABC
A   WXYZ    0            COLUMN_B       DEF
A   WXYZ    0            COLUMN_C      CHI
A   WXYZ    0           COLUMN_C       JKL
A   WXYZ    0           COLUMN_D       MNO

After applying Union on source I got the below table:

We have many other Rows based on Type, ID, Version.

Data After Union(Similar to):.

TYPE    ID     VERSION     COLUMN_A    COLUMN_B    COLUMN_C    COLUMN_D
A    WXYZ       0           ABC         <Null>     <Null>     <Null>
A    WXYZ       0          <Null>        DEF        <Null>    <Null>
A    WXYZ       0          <Null>       <Null>     CHI        <Null>
A    WXYZ       0          <Null>        <Null>     JKL        <Null>
A    WXYZ       0          <Null>        <Null>    <Null>     MNO

OUTPUT I need:

TYPE   ID       VERSION    COLUMN_A    COLUMN_B    COLUMN_C    COLUMN_D
    A     WXYZ         0          ABC         DEF        CHI      MNO
    A     WXYZ         0          ABC         DEF        JKL      MNO

I tried taking max of the Column_A,Column_B,Column_C,Column_D.using an aggregation node. but the problem is Design Studio does not support CV in which maximum of any attriubute is taken.

Can anybody suggest any other way of doing it?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Florian Pfeffer
Nov 28, 2017 at 05:59 AM
0

What are you using to do that? Plain SQL, a Calc. View ...?

Using the search here in that forum would point you to some similar questions and related blog posts as for example the recently discussed Split Values into Columns with a Calculation View. If your columns (A - D) are fix and not dynamic (in an endless way), the answer in the thread would fit your scenario too.

Regards,
Florian

Share
10 |10000 characters needed characters left characters exceeded