Skip to Content
Technical Articles
Author's profile photo Shrikant Patil

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-%20Requirement

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-%20Requirement

Img2 from my personal machine- Requirement

And final Output will be:

Img3-%20Expected%20Final%20Output

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-%20Sorted%20Data

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-%20Output

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/

 

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      Thanks for sharing!

      I'm quite confused by this use case and solution. to be honest... It starts with "Step1: Create Table Function". But why? After "here is a problem" we usually follow with "let's look at possible solutions and find the right one". This is actually the most interesting part of such blog posts. If you meant to write something like "Hey, I just woke up today and wanted to try X for giggles and here is a made-up example that I used" that just say it like that.

      It's acknowledged towards the end that this is a very specific case when you know how many entries are there. Again, why? I can think of several examples where we'd need to combine data from different sources as one "row"(sales document with added delivery information is a very common scenario) but not any of them would assume fixed number N in 1:N relationship.

      Admittedly, I didn't read the whole post (odd formatting makes it more difficult to understand) but right off the bat for this scenario you could use just JOIN or maybe UNION in the worst case. It seems pretty obvious but again, what lead to "step 1" is just not clear.

      If I may offer some wisdom when it comes to writing and programming...

      1. It is a brave move to just post something on global SAP Community where our content shares space with SAP's own posts and semi-professional writers. Kudos for that. Considering that you seem to be a beginner in both writing and development, it might have been more comfortable for you to share the post with a colleague or an experienced Community member first to get their feedback. It is quite common to "start small" and I think it's a great approach for everyone.

      2. It's OK to start with a very specific use case but don't just stop there. This can be the very first step but when you think "OK, I made it work for 1 item, now will it work for 100 items?" you see very quickly all the problems with the initial solution. This is normal development process.

      3. It's very likely that what you're doing has been done before you. Many times. Use that knowledge and build on it instead of trying to reinvent something. For example, there is a good blog post about table functions (I read it before but had no trouble finding it in Google). I think if you read it, your solution would've been different.

      4. Most important part in development (and Marcelo's blog above talks about that) is how to pick the right instrument for the job. Again, this is the part that's missing and I believe it would've been great value for the readers if you shared your thought process next time. E.g. "I considered this or that and for one reason or another I chose X".

      Good luck.

      Author's profile photo Shai Sinai
      Shai Sinai

      I think that this comment deserves a blog post of its own.

      Author's profile photo Shrikant Patil
      Shrikant Patil
      Blog Post Author

      Thank you very much Jelena for your feedback and insights, I really appreciate it.

      Your advice on starting small and building on existing knowledge is also great advice for any developer or writer. It's always a good idea to seek feedback and learn from others who have more experience in a particular area.

      Also, you're absolutely right that selecting the right tool for the job is crucial in development. Explaining why a particular approach was chosen and weighing the pros and cons of different options is always helpful for readers who may encounter similar problems.

      So coming back to WHY table function… Here is the reason & I should have added in the blog as well and I am apologizing for that. I wanted latest data based on Date (FLDATE) for CARRID & CONNID & that’s where RANK function came to picture & that’s why Table Function.

       

      And about the specific case… I have a requirement where a database table contains a maximum number of entries for a specific combination of two fields.

      And below are few blogs where this type of case can be observed.

      https://blogs.sap.com/2020/04/30/transpose-row-set-into-column-name-dynamic-table-creation-in-sap-hana-2.0-sps04/

      https://blogs.sap.com/2019/06/19/single-row-to-multiple-rowstranspose-in-cds-views/

      https://answers.sap.com/questions/12983563/transpose-the-dynamic-rows-in-to-static-columns-in.html

      I tried using CDS with JOINS, UNIONS, MAX, MIN, and more, but I was not able to find a solution. If you have any reference blogs on the same topic, please share them. I would be really glad to explore them.

      Finally, thank you again for the feedback. I will definitely keep these things in mind while programming and writing blogs.

      Have a great day!

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      OK, that makes more sense now. I think you could've used MAX but this is where we usually run into the problems like we want to (a) find the latest record and (b) get some data from it. But as soon as you include MAX, all other fields must be in GROUP BY part, which leads to totally different results that you wanted in (a). There are some workarounds but not great.

      Thanks for the update!

      Author's profile photo Himanshu Gupta
      Himanshu Gupta

      Hello Srikant,

      You can use Join instead of Max on the same table function with as filtering criteria. Max makes sense if you are looking to have the max value with group by. Also, this will be more intensive performance wise. Please try to use more possible best ways.

      Many Thanks / Himanshu gupta