Skip to Content

CDS View: Data as Pivot (cross table)

Hi All,

I need a CDS View, that transfered the data in the following way:


Anyone an idea or an example code?

thanks in advance and best regards

CrossTab2.PNG (14.1 kB)
Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    Oct 05, 2016 at 07:18 PM

    Hi all,

    so, I have found a solution for myself: I'm using a AMDP to get the data in the form I needed:

    class Z_PP_AMDP_WORKCENTER definition

    public

    final

    create public .

    public section.

    INTERFACES if_amdp_marker_hdb.

    CLASS-METHODS:

    get_workcenter_per_order FOR TABLE FUNCTION Z_TF_WORKCENTER.

    protected section.

    private section.

    ENDCLASS.

    CLASS Z_PP_AMDP_WORKCENTER IMPLEMENTATION.

    METHOD get_workcenter_per_order BY database function for hdb language sqlscript

    OPTIONS read-only

    USING ZI_V_PP_FERTLISC.

    return SELECT

    MANUFACTURINGORDER as m_order,

    MAX(CASE WHEN ROW_NUM=1 THEN workcenter ELSE NULL END) AS workcenter_1,

    MAX(CASE WHEN ROW_NUM=1 THEN edate ELSE NULL END) AS enddate_1,

    MAX(CASE WHEN ROW_NUM=2 THEN workcenter ELSE NULL END) AS workcenter_2,

    MAX(CASE WHEN ROW_NUM=3 THEN workcenter ELSE NULL END) AS workcenter_3,

    MAX(CASE WHEN ROW_NUM=4 THEN workcenter ELSE NULL END) AS workcenter_4,

    MAX(CASE WHEN ROW_NUM=5 THEN workcenter ELSE NULL END) AS workcenter_5,

    MAX(CASE WHEN ROW_NUM=6 THEN workcenter ELSE NULL END) AS workcenter_6,

    MAX(CASE WHEN ROW_NUM=7 THEN workcenter ELSE NULL END) AS workcenter_7,

    MAX(CASE WHEN ROW_NUM=8 THEN workcenter ELSE NULL END) AS workcenter_8,

    MAX(CASE WHEN ROW_NUM=9 THEN workcenter ELSE NULL END) AS workcenter_9,

    MAX(CASE WHEN ROW_NUM=10 THEN workcenter ELSE NULL END) AS workcenter_10

    FROM (

    SELECT MANUFACTURINGORDER, workcenter, edate, row_number () over (partition by MANUFACTURINGORDER) as ROW_NUM

    FROM ZI_V_PP_FERTLISC

    ORDER BY MANUFACTURINGORDER )

    GROUP BY MANUFACTURINGORDER;

    ENDMETHOD.

    ENDCLASS.

    So, this AMDP is included in a table function:

    @ClientDependent: false

    @EndUserText.label: 'Table function for Workcenter per Fertigungsauftrag'

    define table function Z_TF_WORKCENTER

    returns

    {

    key m_order : char12;

    workcenter_1 : char8;

    enddate_1 : abap.dats;

    workcenter_2 : char8;

    workcenter_3 : char8;

    workcenter_4 : char8;

    workcenter_5 : char8;

    workcenter_6 : char8;

    workcenter_7 : char8;

    workcenter_8 : char8;

    workcenter_9 : char8;

    workcenter_10 : char8;

    }

    implemented by method

    Z_PP_AMDP_WORKCENTER=>GET_WORKCENTER_PER_ORDER;

    This is working fine for me....

    Best regards

    Thorsten

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 29, 2016 at 10:54 AM

    No one an idea?

    I tried to split the logic to 2 views:

    the first view gets the row number:

    SELECT "FIELD_A", "ATTRIB", row_number () over (partition by "FIELD_A") as ROW_NUM

    FROM SOURCETABLE

    ORDER BY "FIELD_A" )

    GROUP BY "FIELD_A";


    the second view give out the value per FIELD_A:


    SELECT "FIELD_A",

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

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

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

    ........


    but I didn't get a ROW_NUMBER in the first view.


    Anyone can help me?


    best Regards

    Add comment
    10|10000 characters needed characters exceeded