Transpose Data in SAP HANA

[former_member258682]

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?

[former_member607745]

Tried to replicate your requirement and it gets fulfilled by implementing join.

Create 3 projection nodes each with a filter on COLUMN_NAME for column_A,column_B & column_C

Inner join 1 by 1 on ID and Type and only select column "VALUE" out of them,rename each "VALUE" column to COLUMN_A,COLUMN_B & COLUMN_C.

capture.pngcapture.pngcapture.png

[pfefferf]

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