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. Letsss 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.

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.

Img2 from my personal machine- Requirement

And final Output will be:

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.

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:

Img5 from my personal SAP System- Output

Conclusion:

The CDS view “ZDEMO_CDS_ZFLIGHT” was able to consume the data from the table function “ZDEMO_SFLIGHT_TAB_FCT” and provide the expected results. This demonstrates the power and flexibility of using table functions and CDS views together in ABAP programming.

Table functions can be used to provide data from a variety of sources, including external systems, and CDS views can be used to create a unified data model that can be consumed in a standard way by other ABAP programs. This is an important feature of ABAP programming, as it allows developers to create a unified view of data from multiple sources that can be consumed in a consistent manner.

Here are the key points and takeaways from the ABAP code:

• The code defines a table function that returns a table type with the required columns for retrieving flight data, including seat availability.
• The implementation of the table function is done by a method of a class, which executes a SQLScript SELECT statement on the required database table and returns the data to the table function.
• A CDS view is then defined to consume the table function and group the data by airline and flight number.
• The code provides a way to retrieve flight data for a particular airline and flight number, along with seat availability for each date the flight is scheduled to operate.
• The key takeaways from this code are that table functions and CDS views are powerful ABAP constructs that can be used to retrieve and consume data in a structured manner. Additionally, SQLScript can be used to implement complex logic in an efficient and optimized way.

Note- This solution is accurate when you know how many entries will be there in the table with fields combination. (For example- Some finance reports based on BUKRS & LIFNR)

Your inputs are welcome & if you have any suggestions, please feel free to comment.

PS: I am a newbie in writing Blogs, and I am getting better at a time

Also, please follow SAP S/4Hana Environment Topic Page https://community.sap.com/topics/s4hana

Read other post on SAP S4/HANA

Below are a few great blogs I referred to for my optimal solution.

https://blogs.sap.com/2017/12/12/concatenate-multiple-records-in-a-single-field-using-abap-cds-table-function/

https://blogs.sap.com/2019/07/16/transpose-or-merge-multiple-rows-in-one-row-cds-views/

https://blogs.sap.com/2018/11/30/delete-duplicate-entries-in-abap-cds-views-using-table-function-and-sql-window-function/