Skip to Content
Technical Articles

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.

8 Comments
You must be Logged on to comment or reply to a post.
    • Hi Florian,

       

      Thanks for your view,

      I have just illustrated how to merge rows in single one with dummy example. Yes you are right it will ignore the Item. So you may want to add Item in Group By or If you need only header Partners then pass where condition as POSNR = ‘000000’ which i have added just to be more clear.

  • Thanks D. Lande,

    Your blog solved almost my requirement but I am stuck as I also need Names of the respective partners side by side.

    Please help if possible…

    Thanks,

    Raj.

      • Hi Dnyaneshwar,

        You helped to get the Customer number in your requirement in one row, but I also need their names beside that number in the same row which has different customer numbers.

        I tried using joins but it fetches only first customer’s name and copies the same name to others. Also, I have Vendors along in the structure.

        e.g. Below is the row structure I need to achieve.

        VBELN KUNNR(1) NAME1(1) KUNNR(2) NAME1(2) KUNNR(3) NAME1(3)

         

        Thanks,

        Raj S.

         

        • Hi Raj,

           

          You can use CONCAT statement.After THEN in below statement you can use concat (kunnr,name)  and you will get desired results.

          max( case parvw when 'AA' then kunnr end ) as SoldTo,
          • Hi Dnyaneshwar Lande,

            Thanks for your response, but my query still needs some guidance to be successful.

            The requirement is like I have one sales document, which has different partners like sold to party,ship-to party,

            and Transporter.

            VBELN – 9000…1

            KUNNR – 4500…1 (Sold To Party)

            NAME1 – ABC Ltd. (Sold To Party)

            KUNAG – 4500…2 (Ship To Party)

            NAME1 – XYZ Ltd. (Ship To Party)

            LIFNR – 46000….1  (Transporter as Vendor)

            NAME1 – MNO Ltd.  (Transporter as Vendor)

             

            Now in VBPA Table, I get records like

            900..1  | SP | 45000….1

            900..1  | SH | 45000….2

            900..1  | ZT  | 46000…1

             

            Now I need to query like I get 2 different records from KNA1 Table and LFA1 at a time in a single line like:

            900..1  | 45000…1 | ABC Ltd. | 45000…2 | XYZ Ltd. | 46000…1 | MNO Ltd.

            I tried it like you described earlier but I am getting like:

            900..1  | 45000…1 | ABC Ltd. | 45000…2 | ABC Ltd. | 46000…1 | MNO Ltd.

              as select from    likp      as del
                left outer join zlr_entry as lr   on del.vbeln = lr.vbeln
                inner join      vbpa      as prt  on del.vbeln = prt.vbeln
                inner join      kna1      as cust on prt.kunnr = cust.kunnr
                inner join      lfa1      as vend on prt.lifnr = vend.lifnr
            {
              del.vbeln                                          as Delivery_doc,
              del.erdat                                          as Delivery_dat,
              lr.lrnum                                           as LR_NUM,
              lr.lrdat                                           as LR_DAT,
              max( case prt.parvw when 'AG' then prt.kunnr end ) as sold_party,
              cust.name1                                         as SOP_Name,
              max( case prt.parvw when 'WE' then prt.kunnr end ) as ship_party,
              cust.name1                                         as shp_name,
              max( case prt.parvw when 'ZB' then prt.lifnr end ) as broker,
              vend.name1                                         as brkr_nm,
              max( case prt.parvw when 'ZF' then prt.lifnr end ) as transporter,
              vend.name1                                         as trns_nm
            }
            

             

            Please help if you have a better solution for this.

             

            Thanks,

            Raj S.

  • Hi Dnyaneshwar Lande ,

     

    Nice blog.  We have a scenario where we are doing a search across the sales order header VBAK  table in a List Report view.  We also want users to be able to search for orders that have order line items with different divisions.  These divisions are not the division at the sales order header level but at the line item instead – but the search is on the List Report page for the sales order.  In your example, you do the max function on the same entity – but can we apply a search on the child level item.

    e.g.

    Sales Order 123

    Sales Oder line item 10 – division 10

    Sales Order line item 20 – division 40

     

    Sales Order 234

    Sales Oder line item 10 – division 40

    Sales Order line item 20 – division 50

     

    Sales Order 567

    Sales Oder line item 10 – division 10

    Sales Order line item 20 – division 50

     

    We would like to get the sales orders with header level details for line items with division 10 – this should return order 123 and 567

    We would like to get the sales orders with header level details for line items with division 40 – this should return order 123 and 234

    We would like to get the sales orders with header level details for line items with division 50 – this should return order 234 and 567

     

    Is there a way to do this with CDS views without table functions?  We are on ABAP 7.50 and IBM DB6 but the database version does not support table functions.

    Thanks,

    Jay