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.
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
No comments:
Post a Comment