CDS View: Data as Pivot (cross table)

[D039507]

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

[D039507 · Accepted solution]

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

[D039507]

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

[lbreddemann]

Not quite sure what the problem here is.

This looks to me like a classic SQL table pivot for which multiple solutions exist - the search engine of your choice will have it.

One remark though: generating "row ids" is usually a performance killer and should be used only when it's well understood what this "id" means and what it doesn't mean (e.g. it's not a primary key).

[D039507]

Hi Lars,

I need this in a CDS-View and therefore I didn't find a solution. If you knew multiple solutions, please let me know one of them 🙂

best Regards