Wednesday 1 March 2023

Convert Multiple Rows in One Row with different columns in CDS Views

I’ve been searching for a solution for a while now, but I’ve been unable to find one that meets my requirements, even after consulting multiple blogs. It took some effort, but I’ve finally found a solution that fits my needs. Lets goo (Nervously excited)

Introduction


Dealing with multiple rows of data can be challenging, especially when you need to consolidate them into a single row with different columns. Fortunately, SAP CDS provides a powerful solution for performing this data transformation. In this blog post, we’ll show you how to use SAP CDS to convert multiple rows into a single row with different columns.

What is SAP CDS?


SAP CDS (Core Data Services) is a modeling language and framework that allows developers to define data models and access them in a standardized way. CDS views are used to create virtual data models that combine data from different sources, such as tables, views, and other CDS views. CDS views provide a simple and efficient way to query data from SAP systems.

Let’s begin by considering the below Scenario.

SAP S/4HANA, SAP HANA, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Prep, SAP HANA Preparation, SAP HANA Guides, SAP HANA Learning
Img1 from my personal SAP System- Requirement

Now I want to merge rows of ZDEMO_SFLIGHT table based on CARRID & CONNID like FLDATE1, PRICE1, CURRENCY1…FLDATE2 to SEATSOCC2.

SAP S/4HANA, SAP HANA, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Prep, SAP HANA Preparation, SAP HANA Guides, SAP HANA Learning
Img2 from my personal machine- Requirement

And final Output will be:

SAP S/4HANA, SAP HANA, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Prep, SAP HANA Preparation, SAP HANA Guides, SAP HANA Learning
Img3 from my personal machine – Expected Final Output

Step1: Create Table Function.

@EndUserText.label: 'Table Function Demo SFLIGHT'
define table function ZDEMO_SFLIGHT_TAB_FCT
returns
{
  MANDT    : abap.clnt;
  CARRID    : s_carr_id;
  CONNID    : s_conn_id;
  FLDATE    : s_date;
  PRICE     : s_price;
  CURRENCY  : s_currcode;
  PLANETYPE : s_planetye;
  SEATSMAX  : s_seatsmax;
  SEATSOCC  : s_seatsocc;
  ROW_NUM   : abap.char( 2 );

}
implemented by method
  zcl_demo_sflight=>get_data;

This is an example of an ABAP table function called “ZDEMO_SFLIGHT_TAB_FCT”. It returns a table of data with the following columns:

client : abap.clnt (client ID)

CARRID : s_carr_id (airline carrier ID)

CONNID : s_conn_id (flight connection ID)

FLDATE : s_date (flight date)

PRICE : s_price (ticket price)

CURRENCY : s_currcode (currency code)

PLANETYPE : s_planetye (plane type)

SEATSMAX : s_seatsmax (maximum number of seats)

SEATSOCC : s_seatsocc (number of occupied seats)

row_num : abap.char(2) (row number)

The table function is implemented by a method called “get_data” in the ABAP class “ZCL_DEMO_SFLIGHT”. When this function is called, it will execute the “get_data” method and return a table of data with the specified columns.

Step2: Create Class & Method.

CLASS ZCL_DEMO_SFLIGHT DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .
  PUBLIC SECTION.
    INTERFACES IF_AMDP_MARKER_HDB.
    CLASS-METHODS:
      GET_DATA FOR TABLE FUNCTION ZDEMO_SFLIGHT_TAB_FCT.
  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.

CLASS ZCL_DEMO_SFLIGHT IMPLEMENTATION.
  METHOD GET_DATA
        BY DATABASE FUNCTION
        FOR HDB
        LANGUAGE SQLSCRIPT
        OPTIONS READ-ONLY
        USING  ZDEMO_SFLIGHT .
    ITAB =
      select
            MANDT,
            CARRID,
            CONNID,
            FLDATE,
            PRICE,
            CURRENCY,
            PLANETYPE,
            SEATSMAX,
            SEATSOCC,
            ROW_NUMBER() OVER (PARTITION BY carrid, connid order by carrid, connid ) as row_num
        from zdemo_sflight ;
    RETURN
     SELECT MANDT,
            CARRID,
            CONNID,
            FLDATE,
            PRICE,
            CURRENCY,
            PLANETYPE,
            SEATSMAX,
            SEATSOCC,
            ROW_NUM
        FROM :itab
       GROUP BY MANDT,
            CARRID,
            CONNID,
            FLDATE,
            PRICE,
            CURRENCY,
            PLANETYPE,
            SEATSMAX,
            SEATSOCC,
            ROW_NUM;
  ENDMETHOD.
ENDCLASS.

This is the implementation code for the ABAP class “ZCL_DEMO_SFLIGHT” that defines the method “GET_DATA” used to implement the table function “ZDEMO_SFLIGHT_TAB_FCT”.

The class implements the interface “IF_AMDP_MARKER_HDB” which is used to indicate that the class contains a HANA database procedure.

The method “GET_DATA” is implemented as a HANA SQLScript procedure and it reads data from the database table “ZDEMO_SFLIGHT”. The data is then transformed using the “ROW_NUMBER()” function to add a sequential number to each row based on the “CARRID” and “CONNID” fields. Finally, the result is returned as a table using the “RETURN” statement.

Using ROW_NUMBER PARTITION BY, we get data in sorted form.

SAP S/4HANA, SAP HANA, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Prep, SAP HANA Preparation, SAP HANA Guides, SAP HANA Learning
Img4 from my personal Eclipse- Sorted Data

Now we can consume this table function in a CDS view to get appropriate output.

Step3.Create CDS

@AbapCatalog.sqlViewName: 'ZDEMO_SQL_FLIGHT'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'CDS to consume table function'
define view ZDEMO_CDS_ZFLIGHT
  as select from ZDEMO_SFLIGHT_TAB_FCT
{
  CARRID,
  CONNID,
  max (case ROW_NUM when '1' then FLDATE end)    as FLDATE1,
  max (case ROW_NUM when '1' then PRICE end)     as PRICE1,
  max (case ROW_NUM when '1' then CURRENCY end)  as CURRENCY1,
  max (case ROW_NUM when '1' then PLANETYPE end) as PLANETYPE1,
  max (case ROW_NUM when '1' then SEATSMAX end)  as SEATSMAX1,
  max (case ROW_NUM when '1' then SEATSOCC end)  as SEATSOCC1,

  max (case ROW_NUM when '2' then FLDATE end)    as FLDATE2,
  max (case ROW_NUM when '2' then PRICE end)     as PRICE2,
  max (case ROW_NUM when '2' then CURRENCY end)  as CURRENCY2,
  max (case ROW_NUM when '2' then PLANETYPE end) as PLANETYPE2,
  max (case ROW_NUM when '2' then SEATSMAX end)  as SEATSMAX2,
  max (case ROW_NUM when '2' then SEATSOCC end)  as SEATSOCC
}

group by
  CARRID,
  CONNID

This is an example of a CDS view definition that consumes the table function “ZDEMO_SFLIGHT_TAB_FCT”.

The view is named “ZDEMO_CDS_ZFLIGHT” and it has the following columns:

CARRID: the airline carrier ID

CONNID: the flight connection ID

FLDATE1: the flight date for the first row (based on ROW_NUM)

PRICE1: the ticket price for the first row

CURRENCY1: the currency code for the first row

PLANETYPE1: the plane type for the first row

SEATSMAX1: the maximum number of seats for the first row

SEATSOCC1: the number of occupied seats for the first row

FLDATE2: the flight date for the second row (based on ROW_NUM)

PRICE2: the ticket price for the second row

CURRENCY2: the currency code for the second row

PLANETYPE2: the plane type for the second row

SEATSMAX2: the maximum number of seats for the second row

SEATSOCC2: the number of occupied seats for the second row

The view groups the data by “CARRID” and “CONNID” using the “GROUP BY” clause.

Based on row number data will be populated to appropriate fields.

The output of CDS View:

SAP S/4HANA, SAP HANA, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Prep, SAP HANA Preparation, SAP HANA Guides, SAP HANA Learning
Img5 from my personal SAP System- Output

No comments:

Post a Comment