I recently came across a requirement to convert multiple rows to columns in SAP S/4 HANA. Since we have so many capabilities within CDS views and AMDP to perform code pushdown this was achievable using tables functions. If you have suggestions on how this can be done differently please feel free to provide inputs. Examples below are for illustration purpose only, the business scenario details are not exactly depicted.
Since String aggregate(string_agg), is not allowed in CDS views directly, utilized table functions to achieve this.
Here we go with this one –
Requirement:
Material |
Class |
Counter |
Type |
Number |
Y1-5667 |
FRT |
0 |
A1 |
1 |
Y1-5667 |
FRT |
1 |
A2 |
2 |
Y1-5667 |
FRT |
2 |
A3 |
3 |
Y1-5667 |
FRT |
3 |
A4 |
4 |
Y1-5667 |
SUP |
0 |
B1 |
5 |
Y1-5667 |
SUP |
1 |
B2 |
6 |
Y1-5667 |
SUP |
2 |
B3 |
7 |
Y1-5667 |
SUP |
3 |
B4 |
8 |
Material, Class & Counter form the key fields.
Output Required
Material |
A |
B |
Y1-5667 |
FRTA11;FRTA22;FRTA33;FRTA44 |
SUPB15;SUPB26;SUPB37;SUPB48 |
Here multiple rows with same Class were required to be combined and then concatenated into one string as shown above.
Created a CDS view with table function with logic below
@EndUserText.label: 'Process code concatenated'
define table function ZCDS_MATDATA
returns {
mandt
: abap.clnt;
key Material
: matnr;
A
: char200;
B
: char200;
}
implemented by method ZCL_AMDP_MATDATA
=>GET_CONCAT_MATTYPES
;
CLASS zcl_amdp_matdata DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb.
CLASS-METHODS GET_CONCAT_MATDATA for table function ZCDS_BF_MATTYPES.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.
CLASS zcl_amdp_matdata IMPLEMENTATION.
method ZCDS_BF_MATTYPES BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING
TAB1.
return select Mandt,
Material,
string_agg( A, ';' ) as A,
string_agg( B, ';' ) as B
from ( select mandt,
material,
counter,
Max( concat( Class (' ', concat(Type(' ', Number )))) end )
as A,
Max( concat( Class (' ', concat(Type(' ', Number )))) end )
as B
from TAB1
where a.mandt = session_context('CLIENT')
group by mandt, material, counter
)
group by mandt, material;
endmethod.
ENDCLASS.
