topic Re: CDS View Pivot or Tranpose in Application Development and Automation Discussions

CDS View Pivot or Tranpose

Former Member — Mon, 11 Mar 2019 15:42:02 GMT

Hi Experts,

I am trying to develop a simple Summary Report in ABAP. However, due to the very large data volume, we decided to leverage HANA by using SALV-IDA and moving all processing to the DB Level via CDS Views.

Below is a simplified version of what I'm trying to accomplish. The goal is to be able to compare fields one-by-one per Document Item and ultimately display it as Item and Category Summaries. In the example below we see that the Quantity, Amount and Currency Fields are different for Document 1 Item 1.

The complication revolves around the table ZITEMS Table. This Contains Versions (at most 2) of the same Document Item as rows. I figured to be able to do comparisons and aggregates, I need to create an intermediate view which basically transpose/pivot the version and the comparison fields(material,qty,etc).

I'm quite new to SQL Script and SQL programming in general and have no idea how this is done. I'm used to the traditional approaching of pulling everything via OpenSQL and processing in ABAP. I would like to get some pointers on how this can be done.

Thanks!

-Emir

cds-view-problem.jpg(316.9 kB)

Re: CDS View Pivot or Tranpose

Former Member — Tue, 12 Mar 2019 13:16:59 GMT

Solved on my Own.

Solution used LEFT OUTER JOIN on the same table. I did this per field. So you need to create one query for each of the fields and combine them all together via UNION.

Re: CDS View Pivot or Tranpose

viren_sharma — Thu, 29 Oct 2020 08:08:16 GMT

I have the same requirement, can you please me with your way/code how did you achieve this?

Re: CDS View Pivot or Tranpose

theequalizer — Wed, 25 Aug 2021 09:59:07 GMT

i used following approach:

define view ZI_PIVOT( keyField, selectField ) 
as select from xxx{ key abc, fieldAlpha } 
union all select from xxx{ keyField, fieldBeta } 
union all select from xxx{ keyField, fieldGamma }<br>

key1|X|E|Z to

key1|X
key1|E
key1|Z

if you use UNION instead of UNION ALL - the duplicate entries will be removed automatically

Re: CDS View Pivot or Tranpose

RohitSingh — Fri, 21 Oct 2022 05:45:34 GMT

Hi Emir,

Could you please let us know your approach you used to solve this. I have a similar requirement and could use some inputs.