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.
the table has another key, so this is not a solution you should go for, because your order may have partners on positions.
S
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 Raj ,
Can you please elaborate your requirement? Side by side meaning concatenate in single column ?
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.
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.
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
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