In this blog post you will learn how to merge multiple rows into single row with addition of columns.
Introduction:
CDS Views are becoming increasingly popular. The
CDS database
view is the technical foundation of the
CDS view in
ABAP Dictionary. It is a read-only classic database
view. ... In
ABAP Dictionary, the
CDS database
view can be used like any classic structure, such as the type of a substructure, as an include structure, or as the row type of a table type.
CDS Views are really fast and are now able to replace the functionality of reports and are easy to expose. Supports front end annotations too which would really help in designing a Fiori Application with minimal front end coding.
Problem Statement:
SAP Order Partner Table stores multiple row with identifier as
PARVW. CDS needs to merge/transpose data into single row with each new column has respective value.
SAP Table Data
Vbeln Parvw Kunnr
1 AA 10001
1 BB 20002
1 CC 30003
2 AA 50001
2 BB 60001
Expected Output
OrderNo SoldTo ShipTo BillTo
1 10001 20002 30003
2 50001 60001
Target Audience:
Beginners to CDS View Development
Solution:
@AbapCatalog.sqlViewName: 'DEMO_TEST'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Transpose Multiple Rows Into Single Row'
define view DEMO_TRANSPOSE
as select from vbpa
{
vbeln as OrderNo
max( case parvw when 'AA' then kunnr end ) as SoldTo,
max( case parvw when 'BB' then kunnr end ) as ShipTo,
max( case parvw when 'CC' then kunnr end ) as BillTo
}
where posnr = '000000'
group by vbeln
With Above code
CASE will help in creating multiple columns and and
MAX will merge multiple rows into single by grouping
Order Number with each column has correct value as expected.
Above solution will work when no multiple rows exists with same Partner Type. Make sure your case fits into the unique row per Group By Column name with respect to Case Column Name .
Summary :
In this blog post one will learn how to merge multiple rows into single row with help of
CASE and
MAX function of CDS views.
