Skip to Content
Technical Articles
Author's profile photo Dnyaneshwar Lande

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.

Assigned Tags

      9 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Florian Henninger
      Florian Henninger

      the table has another key, so this is not a solution you should go for, because your order may have partners on positions.

      S

      Author's profile photo Dnyaneshwar Lande
      Dnyaneshwar Lande
      Blog Post Author

      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.

      Author's profile photo Raj Sagar
      Raj Sagar

      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.

      Author's profile photo Dnyaneshwar Lande
      Dnyaneshwar Lande
      Blog Post Author

      Hi Raj ,

       

       

      Can you please elaborate your requirement?   Side by side meaning concatenate in single column  ?

      Author's profile photo Raj Sagar
      Raj Sagar

      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.

       

      Author's profile photo Dnyaneshwar Lande
      Dnyaneshwar Lande
      Blog Post Author

      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,
      Author's profile photo Raj Sagar
      Raj Sagar

      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.

      Author's profile photo Jay Malla
      Jay Malla

      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

       

       

       

       

       

       

       

       

       

       

      Author's profile photo Amit Biswas
      Amit Biswas

      Nice Blog Dnyaneshwar Lande !

      I have another situation where I don't know the values, so how can I make columns from dynamic values?

      Like:

      Col:1     Col:2     Col:3         Col:4

      Key1     Key2     Value 11    Value   21

      Key1     Key2     Value 12    Value   22

      Key1     Key2     Value 11    Value   23

      Key1     Key2     Value 12    Value   24

       

      I need to make:

      Col:1     Col:2    Value 11         Value 12

      Key1     Key2      Value   21      Value   22

      Key1     Key2      Value   23      Value   24

       

      So the Values from Col:3(which is not fixed values) become Column and respective Col:4 values under it.

       

      Thanks and Regards

      Amit