Transpose Or Merge Multiple Rows In One Row CDS Views

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.